SQL Server – refresh all Views

The strict definition of a SQL Server View is a virtual table that presents table(s) data.

Using the sp_refreshView – updates the metadata of the the view. Any view depends on underlying objects – and from time to time these objects are changed.

Typically this is useful when a column is added or dropped.

Sometimes in a performance troubleshooting emergency , there may be a requirement to refresh all Views in a database. This may be part of a set sequence – prior to isolating various problems – such as Rapid Tuning This script will list out all Views in a database , and execute the sp_RefreshView

DECLARE @view AS VARCHAR(255) ;
DECLARE ListOfViews CURSOR FOR 

 SELECT  TABLE_SCHEMA + '.' +TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 

    WHERE TABLE_TYPE = 'VIEW' 

    AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0 

    ORDER BY TABLE_SCHEMA,TABLE_NAME 

OPEN ListOfViews 

FETCH NEXT FROM ListOfViews 

into @view 

WHILE (@@FETCH_STATUS <> -1) 

 BEGIN FETCH NEXT FROM ListOfViews 

INTO @view 

 BEGIN TRY

     EXEC sp_refreshview @view;

     PRINT @view;

END TRY

BEGIN CATCH

     PRINT 'recorded error in this refreshView on : ' + @view;

END CATCH;

 FETCH NEXT FROM ListOfViews 

INTO @view 

END 

CLOSE ListOfViews; 
DEALLOCATE ListOfViews;

Read More 

SQL Server Rapid Tuning - SQL Server DBA

SQL Server Materialized Views - SQL Server DBA

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required