Refreshing View and Recompiling Stored Procs

When working on a system that uses views that rely on other views or stored procs that rely on views/tables that may have changed, it is useful to refresh the views in the database to prevent binding errors. It is much easier to refresh all of your views and stored procs versus identifying which views and stored procs are dependent on your change.

Refreshing Views

The below code will automatically refresh all views in a given database. If your changes broken any of the binding then an error will be generated and the process will end. So, if you have broken multiple views you will need to fix a broken view, re-run the script, and check for errors.

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

Refreshing Views that have Errors or the SCHEMABINDING Option

You cannot refresh a view that has the schema binding attribute set. Your options are to either ignore the error or to remove the attribute and refresh the view.

The below code creates a script for you to run. It adds "GO" statements between each script so it will ignore errors. You can then look at the views that failed and determine wht

SET rowcount 0
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 'sp_refreshview ' + @name
Print 'GO'
FETCH NEXT FROM cursor_views INTO @name
END

CLOSE cursor_views
DEALLOCATE cursor_views

After executing the script, this is the script it will create. Run this script and check for any errors.

sp_refreshview MYVIEW1
GO
sp_refreshview MYVIEW2
GO


If any views have WITH SCHEMABINDING set, you will receive this error message.
Msg 8197, Level 16, State 8, Procedure sp_refreshview, Line 1
The object 'MYVIEW1' does not exist or is invalid for this operation.


Recompiling Stored Procs
Recompiling stored procs is done in a similar fashion. To recompile all stored procs you can run this script.

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

OPEN cursor_procs
DECLARE @name sysname

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


It will return the follow message, which indicates that the stored proc will be recompiled the next time it is executed.

Note: Be sure to check the messages carefully to ensure that your changes did not break any bindings.

Recompiling proc: myProc1
Object 'myProc1' was successfully marked for recompilation.
Recompiling proc: myProc2
Object 'myProc2' was successfully marked for recompilation.