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

Category SQL Sub Category Database State

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