When you create a view, SQL Server stores metadata information describing the view, its columns, security, dependencies, and so on. Schema changes in underlying objects are not reflected in the view’s metadata information. After applying such schema changes, it’s a good practice to refresh the view’s metadata information using the sp_refreshview stored procedure so that the changes will be reflected in the view.

To demonstrate what can happen when you make schema changes and don’t refresh the view’s metadata information, first run the following code, which creates the table T1 and the view V1:

USE tempdb;
GO
IF OBJECT_ID('dbo.V1') IS NOT NULL
  DROP VIEW dbo.V1;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1(col1 INT, col2 INT);
INSERT INTO dbo.T1(col1, col2) VALUES(1, 2);
GO
CREATE VIEW dbo.V1
AS

SELECT * FROM dbo.T1;
GO

As a practice, avoid using * in your SELECT statements. I’m using it here just for demonstration purposes. When V1 was initially created, SQL Server stored metadata information about the columns that existed at that point in time–col1 and col2. If you query the view, you get the output shown in Table 5-3 returning both columns:

SELECT * FROM dbo.V1;

Next add a column to T1:

ALTER TABLE dbo.T1 ADD col3 INT;

The schema change in T1 was not reflected in the view’s metadata information. As far as SQL Server is concerned, the view still has just two columns. If you query the view again, you still get the output shown in Table 5-3:

SELECT * FROM dbo.V1;

To refresh the view’s metadata information, run the sp_refreshview stored procedure against V1:

EXEC sp_refreshview 'dbo.V1';

Query V1 again, and you will get the output shown in Table 5-4, which includes the new column–col3:

SELECT * FROM dbo.V1;

This is just one example where a schema change in the underlying objects is not reflected in the view’s metadata information. You might find it to be a good practice to refresh all views’ metadata information after applying schema changes to objects in the database. To avoid the tedious process of writing the sp_refreshview statements you can use the following query:

SELECT N'EXEC sp_refreshview '
     + QUOTENAME(VIEW_NAME, '''') + ';' AS cmd
FROM (SELECT QUOTENAME(TABLE_SCHEMA)
       + N'.' + QUOTENAME(TABLE_NAME) AS VIEW_NAME
     FROM INFORMATION_SCHEMA.VIEWS) AS V
WHERE OBJECTPROPERTY(OBJECT_ID(VIEW_NAME), 'IsSchemaBound') = 0;

The query will generate as its output the lines of code with the sp_refreshview statements against all views in the database that are not schema-bound.

Warning

Make sure that you examine the output carefully before running it. Someone with permission to create views can maliciously plant specially crafted view names to subvert this maintenance code into doing damage.

When you’re done, drop V1 and T1:

USE tempdb;
GO
IF OBJECT_ID('dbo.V1') IS NOT NULL
  DROP VIEW dbo.V1;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO