How to update the metadata of a non schema bounded view.

Problem: How to refresh the metadata of a non-schema bounded views.

Solution: sp_refreshview

Whenever there is a schema change of an underlying table, the view will not get the updates until they are schema bound or metadata protected.

For doing this metadata updates or schema alteration changes of underlying tables, we have to fire the sp_refreshview system procedure.

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Example:

In this article I am going to show you how we can refresh the underlying metadata of a view.

Example 1:

To demonstrate refreshing the metadata of a view.

I have created a view which has all the columns of a table, once we alter the schema to check if there are changes. Later, the sp_refreshview system stored procedure is executed on the view to update the metadata.




-- Dropping view if already exists.

IF  EXISTS(SELECT 1 FROM sys.views where name ='View_tableA' and schema_name(schema_id)='dbo')

DROP VIEW  [dbo].[View_TableA]

GO

-- creating a view to add all columns

CREATE VIEW [dbo].[View_TableA]

AS

SELECT *

FROM dbo.TableA

GO

-- tableA schema changes adding columns

IF  NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS where table_name ='TableA' and table_schema='dbo' and column_name ='Col4')

BEGIN

ALTER TABLE dbo.TableA

ADD col4 VARCHAR(10) DEFAULT 'Test'

END

-- selecting tables, views before refreshing the views

SELECT * FROM dbo.View_TableA

SELECT * FROM dbo.TableA

-- Refershing the view.

EXECUTE sp_refreshview N'dbo.view_tableA'; 

-- selecting view after refreshing the views

SELECT * FROM dbo.View_TableA

Featured Script:

This particular script executes the views which are dependent on the table and changes the metadata underlying.

SELECT DISTINCT 'EXEC sp_refreshview ''' + NAME + ''''

FROM sys.objects AS so

INNER JOIN sys.sql_expression_dependencies AS sed

                ON so.object_id = sed.referencing_id

WHERE so.type = 'V'

AND sed.referenced_id = OBJECT_ID('dbo.tableA'); --change the table name to your convenience.

On other hand if you want to know how we can prevent this to happen. 

please read the following article.

how to protect your views