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 object_schema_name(a.object_id), a.[Name]
FROM sys.objects a
WHERE a.is_ms_shipped = 0
       and a.[type] in ('P', 'U')
ORDER BY 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 command
  --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
  --print @CMD1
  EXEC (@CMD1)
 
  -- 8 - Descend through the database list
  SELECT @TableListLoop = @TableListLoop - 1
end

Reference: Script to Recompile All SQL Server Database Objects