Refreshing SQL Server views

System stored proc sp_refreshview 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. If a view is not created with schemabinding,

This stored proc should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried. User should requires ALTER permission on the view and REFERENCES permission on common language runtime (CLR) user-defined types and XML schema collections that are referenced by the view columns.

Although, if views are using * instead of columns name then sp_refreshview will not produce any error after deleting some columns from base table.

The following example refreshes the metadata for the view Sales.vIndividualCustomer.

USE AdventureWorks2012; GO EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

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 AdventureWorks2012; 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');