sp_refreshview (Transact-SQL)
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 AdventureWorks2008R2; 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.Person 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.Person.
USE AdventureWorks2008R2;
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.Person');
