How to recompile all views in SQL Server

DECLARE @viewname varchar(128)

DECLARE @viewname_header varchar(128)

DECLARE vnames_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'v' OPEN vnames_cursor FETCH NEXT FROM vnames_cursor INTO @viewname WHILE (@@fetch_status <> -1)

BEGIN IF (@@fetch_status <> -2)

BEGIN SELECT @viewname_header = 'Refreshing ' +RTRIM(UPPER(@viewname))

PRINT @viewname_header EXEC ('sp_refreshview ' + @viewname )

END

FETCH NEXT FROM vnames_cursor INTO @viewname

END

PRINT ' '

PRINT ' '

SELECT @viewname_header = '************* NO MORE VIEWS TO REFRESH *************'

PRINT @viewname_header PRINT ' '

PRINT 'All Views Have Been Refreshed.'

DEALLOCATE vnames_cursor

Comments

Find out more