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)
SELECT u.[Name], o.[Name]
FROM sys.objects o
INNER JOIN sys.schemas u ON o.schema_id  = u.schema_id
WHERE o.Type = 'U'
ORDER BY o.[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 command
  SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @OwnerNamequotename(@OwnerName) + '.' + @TableName + ']'quotename(@TableName) + char(13)

  -- 7 - Execute the final string to complete the backups
  -- SELECT @CMD1
  EXEC (@CMD1)

  -- 8 - Descend through the database list
  SELECT @TableListLoop = @TableListLoop - 1
end

Reference: Script to Recompile All SQL Server Database Objects