Recompile all Stored Procedures in database.

If you make some performance turning on existing database, all the change may not reflect in existing stored procedures. Your existing SPs may not take all changes to schema, indexes, table partitioning etc. SPs may not give you the performance results you are expecting. So it is wise to recompile all the stored procedures. We can use sp_recompile system SP to recompile one stored procedure. However  if you want recompile all SPs in database then we need a script. Below is the script you can execute to recompile all SPs in your database.


-- Use <>
DECLARE @SPName AS VARCHAR(255)
-- Cursor fo reading all Stored Procedures in database
DECLARE cursor_AllSPs CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE xtype = 'P'


OPEN cursor_AllSPs


FETCH NEXT FROM cursor_AllSPs into @SPName


WHILE (@@FETCH_STATUS <> -1)
 BEGIN
  FETCH NEXT FROM cursor_AllSPs INTO @SPName
  -- sp_recompile will mark SP for recompile
  EXEC sp_recompile @SPName
 END
CLOSE cursor_AllSPs
DEALLOCATE cursor_AllSPs
 
The above script will mark all stored procedures for compilation. When we execute SPs next time, those will be compiled.