SQL Server 2008–Recompile all Stored Procedures

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