Explicitly refreshing Views in database


A frequent change in underlying object upon which the views depends the metadata of the view gets outdated. This might requires to explicitly refreshing the views in the database. A simple script to refresh all views from the database. 

DECLARE @varSQL VARCHAR(MAX) 
DECLARE cur_View CURSOR 
FOR   SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
OPEN cur_View
FETCH NEXT FROM cur_View INTO @varSQL 

WHILE @@FETCH_STATUS = 0 BEGIN 
      PRINT 'Refreshing View... ' + @varSQL 
      SET @varSQL = 'EXECUTE sp_refreshview ''' + QUOTENAME( @varSQL ) + ''' ' 
      EXECUTE ( @varSQL )  

      IF @@ERROR > 0 
      RAISERROR( 'Error while refreshing view... %s', 16, 1, @varSQL )

      BREAK 

FETCH NEXT FROM cur_View INTO @varSQL
END
CLOSE cur_View
DEALLOCATE cur_View

GO 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s