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.
A. Updating the metadata of a view
The following example refreshes the metadata for the view Sales.vIndividualCustomer.
USE AdventureWorks; GO EXECUTE sp_refreshview N'Sales.vIndividualCustomer';
B. Creating a script that updates all views that have dependencies on a changed object
Assume that the table Person.Contact was changed in a way that would affect the definition of any views that are created on it. The following example creates a script that refreshes the metadata for all views that have a dependency on table Person.Contact.
USE AdventureWorks; GO 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('Person.Contact');