Recompile ALL stored procedures in a database

Sometime after database maintenance such as update statistics or re-index need arises to recompile all stored procedures in that database. Following script can be used to recompile ALL stored procedures in a database




SET NOCOUNT ON

DECLARE @SPName varchar(128)
DECLARE @Owner varchar(128)
DECLARE @CMD1 varchar(8000)
DECLARE @TableListLoop int
DECLARE @List table
(RecID int IDENTITY (1,1),
OwnerName varchar(128),
TableName varchar(128))

INSERT INTO @List(OwnerName, TableName)
SELECT u.[Name], o.[Name]
FROM sys.objects o
INNER JOIN sys.schemas u
 ON o.schema_id  = u.schema_id
WHERE o.Type = 'P' --and o.name not like '%retire%'
ORDER BY o.[Name]


SELECT @TableListLoop = MAX(RecID) FROM @List


WHILE @TableListLoop > 0
 BEGIN

 SELECT @SPName = TableName,
 @Owner = OwnerName
 FROM @List
 WHERE RecID = @TableListLoop

 SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @Owner + '.' + @SPName + ']' + char(13)

 -- SELECT @CMD1
 EXEC (@CMD1)


 SELECT @TableListLoop = @TableListLoop - 1
END

SET NOCOUNT OFF
GO