Sp_RefreshView

Problem:

Sometimes metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

Solution:

Since the metadata for the referenced table columns is persisted in the database, changes to the referenced objects will not get reflected until the view is refreshed using the system stored procedure sp_refreshview.

Syntax:

USE database_name

EXEC sp_refreshview [view_name]

GO

The following script refreshes the metadata for all the views that have dependencies on a changed object

SELECT DISTINCT 'EXEC sp_refreshview ''' + a.name + ''''

FROM sys.objects a INNER JOIN sys.sql_dependencies b

ON a.object_id = b.object_id

WHERE type = 'V'

AND b.referenced_major_id = object_id('tablename')