SQL Recompile All Stored Procedures
The following is an example of how to force a recompile of stored procedures.
-- 1 - Declaration statements for all variables DECLARE @TableName varchar(128) DECLARE @OwnerName varchar(128) DECLARE @CMD1 varchar(8000) DECLARE @TableListLoop int DECLARE @TableListTable table (UIDTableList int IDENTITY (1,1), OwnerName varchar(128), TableName varchar(128))-- 2 - Outer loop for populating the database names INSERT INTO @TableListTable(OwnerName, TableName) SELECTu.[Name],object_schema_name(a.object_id),o.[Name]a.[Name] FROM sys.objectso INNER JOIN sys.schemas u ON o.schema_id = u.schema_ida WHEREo.Typea.is_ms_shipped ='U'0 and a.[type] in ('P', 'U') ORDER BYo.[Name]a.[type], a.[Name] -- 3 - Determine the highest UIDDatabaseList to loop through the records SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable-- 4 - While condition for looping through the database records WHILE (@TableListLoop > 0) begin-- 5 - Set the @DatabaseName parameter SELECT @TableName = TableName, @OwnerName = OwnerName FROM @TableListTable WHERE UIDTableList = @TableListLoop-- 6 - String together the final backup commandSELECT--set @CMD1 = 'EXEC sp_recompile ' + quotename(@OwnerName + '.' + @TableName) set @CMD1 = 'EXEC sp_recompile ''' + quotename(@OwnerName) + '.' + quotename(@TableName) + '''' -- 7 - Execute the final string to complete the backups-- SELECT--print @CMD1 EXEC (@CMD1)-- 8 - Descend through the database list SELECT @TableListLoop = @TableListLoop - 1 end
Reference: Script to Recompile All SQL Server Database Objects