SQL Server Lesson 2 : sp_refreshview system stored procedure

When a new column is added to a table being referenced by a view, the new column will not automatically be reflected to the view, especially if the view is doing a SELECT * from the table.

For the new column to be reflected on the view, you have to refresh the definition of the view using the sp_refreshview system stored procedure. The sp_refreshview system stored procedure refreshes the metadata for the specified view.

Let us check this by example.

Create Table Customers:
CREATE TABLE [dbo].[Customers] (
[CustomerID] INT,
[CustomerName] VARCHAR(100),
[Address] VARCHAR(100)
)

INSERT INTO [dbo].[Customers] VALUES(100, 'Kiran Marke', 'Kalyan');
INSERT INTO [dbo].[Customers] VALUES(101, 'Ankit Jain', 'Ahmedabad');

CREATE VIEW [dbo].[CustomersView]
AS
SELECT * FROM [dbo].[Customers]

Now just do this
SELECT * FROM [dbo].[CustomersView]

This will give all records from the view with all the columns of [dbo]. [Customers] table.

Now let's add one more column to to above table
ALTER TABLE [dbo].[Customers] ADD [Gender] CHAR(1)
UPDATE [dbo].[Customers] SET [Gender] = 'M'

Now once again perform SELECT * FROM [dbo].[CustomersView]

This view will not include the new [Gender] column in the output.
EXECUTE sp_refreshview '[dbo].[CustomersView]'
After issuing this statement, the view will now include the new [Gender] column in the output.