Monday, February 27, 2012

Refresh All Views - SQL Server

Here is a stored procedure that refreshes all views -
CREATE PROCEDURE dba.RefreshAllViews
AS
DECLARE @ViewName NVARCHAR(max)
DECLARE @SQL NVARCHAR(max)

DECLARE RefreshViews CURSOR
FOR
SELECT [name] AS ViewName
FROM sys.VIEWS

OPEN RefreshViews

FETCH NEXT
FROM RefreshViews
INTO @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''V'' AND name = ''' + @ViewName + ''')
   BEGIN
  exec sp_refreshview N''dbo.' + @ViewName + '''END'

 EXEC (@SQL)

 FETCH NEXT
 FROM RefreshViews
 INTO @ViewName
END

CLOSE RefreshViews

DEALLOCATE RefreshViews
GO