Just a useful script to recompile and check all Stored Procedures in a SQL Server Database, if you have done a lot of refactoring.
declare @Schema_Pattern nvarchar(776) = NULL -- Owner name Pattern for Like, Null for all
declare @Proc_Name_Pattern nvarchar(776) = Null -- Proc Pattern for Like, Null for all
SET NOCOUNT ON
-- First do the user databases specified in the control table DECLARE ProcCur CURSOR READ_ONLY FOR
SELECT ROUTINE_SCHEMA
, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE 0=OBJECTPROPERTY(object_id(ROUTINE_SCHEMA + '.'
+ ROUTINE_NAME), 'IsMsShipped')
AND ROUTINE_TYPE = 'PROCEDURE'
AND (@Schema_Pattern IS NULL
OR ROUTINE_SCHEMA LIKE @Schema_Pattern)
AND (@Proc_Name_Pattern IS NULL
OR ROUTINE_NAME LIKE @Proc_Name_Pattern)
DECLARE @Owner nvarchar(776)
, @Proc nvarchar(776)
, @SQL nvarchar(4000)
, @NumP int
, @NumE int
, @myError int
, @myRowcount int
SELECT @NumP = 0 , @NumE = 0
OPEN ProcCur
FETCH NEXT FROM ProcCur INTO @Owner, @Proc
WHILE (@@fetch_status <> -1) BEGIN
IF (@@fetch_status <> -2) BEGIN
SELECT @SQL = 'exec sp_recompile ''' + @Owner + '.' + @Proc + ''''
PRINT 'Running: ' + @SQL
EXEC (@SQL)
SELECT @myError = @@Error
IF @myError = 0 BEGIN
SET @NumP = @NumP + 1
END
ELSE BEGIN
PRINT 'Error Marking ' + @Owner + '.' + @Proc + ':' + convert(varchar(20), @myError)
SET @NumE = @NumE + 1
END
END
FETCH NEXT FROM ProcCur INTO @Owner, @Proc END
CLOSE ProcCur
DEALLOCATE ProcCur
