SQL Server – refresh all Views

12 January,2011 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – refresh all Views


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer