Refreshing a View

When 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 are reflected in the view.

Example:

Create table:

USE tempdb;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1;

CREATE TABLE dbo.Table1(col1 INT, col2 INT);

INSERT INTO dbo.Table1(col1, col2) VALUES(1, 2);

GO

Create View

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

Go

CREATE VIEW dbo.RefreshView

AS

SELECT * FROM dbo.Table1;

GO

select * from RefreshView

col1      col2

1          2

Add Column to the table

ALTER TABLE dbo.Table1 ADD col3 INT;

The schema change in T1 was not reflected in the view’s metadata information

select * from RefreshView

col1      col2

1          2

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

EXEC sp_refreshview 'dbo.RefreshView';

select * from RefreshView

col1      col2      col3

1          2          NULL

When done delete the table and view.

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL

DROP TABLE dbo.Table1;

Video link: