View is not showing newly added column in table

Hi all,
This is for the people who still use “Select * From Table” in their SQL Views. I don’t know what is the reason that you are using this way to create your views, but you could be the victim of this scenario (see… what title says).

Let me show you how you can solve this issue… in-case you come across one.

When this happens??
You make changes in table which is the source of your view. Changes like modifying existing column definition, adding new columns or removing existing columns from table could cause this issue.

Where can I see this issue??
Check the meta-data of your table and view for comparison. (Tip: You can either use traditional method using SYS.OBJECTS joins SYS.COLUMNS or you can check using INFORMATION_SCHEMA.COLUMNS)

What is the solution?
There are different ways to circumvent this issue.

1. Drop and re-create a view definition each time when there is a change in base/source table.
2. You can choose to refresh the view definition using “sp_refreshview” stored procedure.
3. Using C#/VB code + SQL Server notification services, you can track changes of table definition. And once that happen, you re-create/refresh view definition.
4. Using your custom framework in SQL Server (probably with Triggers, which is not a good idea) to track object modifications, you can see when the table definition changes and refresh the view definition accordingly.
5. [Best & Recommended] create a view definition with all required columns of source table and using “WITH SCHEMABINDING” option.

Let me show you the quick demo on checking the issue and implement the quick-solution:
1. Create a Table and View. (In your case, this must be already present as you are facing issue.)

2. Check the object’s (Table and View) schema definitions. You will notice that table has 1 column (according to my example) and it’s available in view.