View does not list all the columns in the table

Recently I came across a question where the user was unable to view all the columns in the underlying table while querying a view. This issue cropped up when one more column was added to the underlying table. Let me explain the scenario and the fix with the below example.

I will create a table which has 3 columns in it.

CREATE TABLE [dbo].[testdata](
	[SlNo] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Address] [varchar](50) NOT NULL
) ON [PRIMARY]

I will create a view which selects all columns from the testdata table.

CREATE VIEW [testdata_view]
AS
SELECT * FROM testdata

The result of select * from testdata_view looks like this.

Now I will add a column named Zip to the testdata table.

ALTER TABLE testdata ADD zip varchar(50)

select * from testdata_view does not list the new column added, instead lists only 3 columns.

Similarly when a column is dropped in the underlying table the view will throw an error.

Msg 4502, Level 16, State 1, Line 1
View or function 'dbo.testdata_view' has more column names specified than columns defined.

This issue happens because the view definition does not get updated automatically when the definition of the underlying table changes. In the example the definition of the view is select * from testdata. Hence there is no point modifying the view to incorporate the new column. sp_refreshview comes to the rescue here. This will refresh the view definition to include the changes to the underlying table.

EXEC sp_refreshview 'testdata_view'

After executing this query, the view will include the newly added to column in the result.

This issue can be avoided if the WITH SCHEMABINDING attribute is included while creating the view. This will not allow the columns to be dropped on the underlying table without modifying the related view. But the drawback is that select * cannot be used in the view if with schemabinding attribute is used in the definition. The column names must be explicitly specified in the view definition.

If the table definition changes for any views which have select * from … in the definition, the only option to fix it is running sp_refreshview against that view.  This query can be used to find out views which have select * in their definition on a given database.

SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION
LIKE '%SELECT * FROM%'