Recompile all stored procedures, user-defined functions and views in the database

Category SQL Sub Category Database State
Bookmark and Share
This script will recompile all stored procedures, user-defined functions and views in the database. This will refresh stored procedures execution plans stored in memory in order to reflect recent schema changes. Previous structures for instance in table functions can be held which will not represent new table structures correctly. So as a process it is good to run this when deploying new versions of the database.
The script will leave all these stored procedures, views and functions in a state that next time they are used, they will get compiled and a fresh query plan will be generated.

Copy Code
/* Recompile Procs, Views, UDF In The Database */ DECLARE proccurs CURSOR FOR SELECT [name] FROM sysobjects WHERE xtype in ('p', 'v', 'fn') OPEN proccurs DECLARE @pname VARCHAR(400) FETCH NEXT FROM proccurs INTO @pname WHILE @@fetch_status = 0 BEGIN EXEC sp_recompile @pname FETCH NEXT FROM proccurs INTO @pname END CLOSE proccurs DEALLOCATE proccurs
Share the love
Bookmark and Share