Rebuild or recompile all views

,

This is a simple little script, but may come in handy for some. During our database upgrades we always run a series of utility steps to make sure our code is in sync and functioning properly based on the revisions that have been made for each release. This script is one of those run to recompile all views to catch any "SELECT *" views, or views where the underlying schema has changed and we missed changing the view.

This can be added as one of your steps to your deployment SQL.

-----------------------------------------------------------------------------
-- REFRESH ALL VIEWS
-----------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @SQL varchar(max) = ''
SELECT @SQL = @SQL + 'print ''Refreshing --> ' + name + '''
EXEC sp_refreshview ' + name + ';
'
  FROM sysobjects 
  WHERE type = 'V' and name like 'vw_%'  --< condition to select all views, may vary by your standards
--SELECT @SQL
EXEC(@SQL)
go

Rate

5 (4)

Share

Share

Rate

5 (4)