Refreshing Views and Recompiling Stored Procs

,

When a table is modified by changing the order of fields or changing field type or adjusting field sizes etc, some of the dependent views (or views on views on views :)) may become invalid as they store some metadata in order to run more efficiently. These views would need to be refreshed with new metadata. Stored procedures store their execution plans as metadata as well, and it is a good idea to recompile them if the underlying tables have changed. This script goes through all views/stored procedures and markes them for recompilation or refreshes them. Use SQL Query analyser or SQL Server management studio to run it against a SQL Server 2000/2005 database. It will also notify you of any potential errors within the views.

DECLARE cursor_views CURSOR FOR 
SELECT [name] FROM sysobjects WHERE xtype='V'
FOR READ ONLY

OPEN cursor_views
DECLARE @name sysname

FETCH NEXT FROM cursor_views INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT 'Refreshing view: '+@name
    EXECUTE sp_refreshview @name
    FETCH NEXT FROM cursor_views INTO @name
END

CLOSE cursor_views
DEALLOCATE cursor_views


DECLARE cursor_procs CURSOR FOR 
SELECT [name] FROM sysobjects WHERE xtype='P'
FOR READ ONLY

OPEN cursor_procs

FETCH NEXT FROM cursor_procs INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT 'Recompiling proc: '+@name
    EXECUTE sp_recompile @name
    FETCH NEXT FROM cursor_procs INTO @name
END

CLOSE cursor_procs
DEALLOCATE cursor_procs

Rate

4.86 (7)

Share

Share

Rate

4.86 (7)