Sometimes after massive changes in MSSQL database, 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.
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
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
