Keith Rull

I've been doing a  lot of code deployments lately and I've come across several occasions wherein my stored procedures wouldn't run as fast as expected compared to it's previously known execution time. The problem lies on the statistics not getting updated after change has been made against an index or other object that may affect efficiency. Since stored procedures are compiled, recompiling them would update these statistics.

Here's a remark about sp_recompile taken from the MSDN website

"The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries."

The syntax is pretty straight-forward:

EXEC sp_recompile '<name of your sp>'

Once executed the stored procedure would then be marked for recompilation and would then be recompiled on the next execution. Niffty huh?!

But what if I want to recompile all my stored procedures? Well, fear not! You can use a cursor that would iterate on all the stored procedure in your current database and execute an sp_recompile against all of them. Below is the script to accomplish this task:

--Recompile all stored procedures on the current database
DECLARE @StoredProcedureName AS VARCHAR(255)

DECLARE listOfStoredProcedure CURSOR FOR
SELECT [Name] FROM sysobjects 
WHERE XTtype = 'P'


OPEN listOfStoredProcedure

    FETCH NEXT FROM listOfStoredProcedure into @StoredProcedureName

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

        FETCH NEXT FROM listOfStoredProcedure INTO @StoredProcedureName
        EXEC sp_recompile @StoredProcedureName

    END

CLOSE listOfStoredProcedure

DEALLOCATE listOfStoredProcedure

GO

HTH

*Note: sp_recompile can also recompile triggers