MSSQL: Recompile Stored Procedures, Views, Functions
Sometimes after massive MSSQL schema update to online scalable production environment there is a need to recompile all stored procedures, user-defined functions and views in the database in order to MSSQL will refresh stores procedures execution plans stored in memory in order to reflect recent schema changes. Below is a small MSSQL code snipped written solely for maintenance purposes. It goes through database objects and performs recompilation using sp_recompile system stored procedure.
/*
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(60)
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
This MSSQL code snipped can easily be implemented as a stored procedure.
Sunday, November 30, 2003 1:54 PM
