Recompile All Databases stored Procedures

  • Comments posted to this topic are about the item Recompile All Databases stored Procedures

  • This could be useful as we have a need to do this on occasion.

  • 1. Should we mark all procedures for recompilation, when upgrading to a new server version?

    2. When the database is changed, like adding indexes or changing data in indexed columns, is the procedures automatic marked for recompilation?

    This script come in handy 🙂

  • The script is not work when the Stored Procedure are in a specific schema.

    USE [DatabaseName] EXEC sp_recompile [Schema.Storedprocedure]

    INSERT INTO @TblTable



    , RecompileStmt





    , N'Select N' + CHAR(39) + 'USE [' + @name

    + '] EXEC sp_recompile ['+ CHAR(39)+'+SPECIFIC_SCHEMA+' +CHAR(39)+'.' + CHAR(39)

    + '+SPECIFIC_NAME+' + CHAR(39) + ']' + CHAR(39)

    + ' from [' + @name

    + '].INFORMATION_SCHEMA.ROUTINES where routine_type = '

    + CHAR(39) + 'PROCEDURE' + CHAR(39)


  • Based on this excerpt from MSDN the SP_Recompile will just clear the plan cache for the associated object.

    The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run.


    This means you've essentially developed a script that replicates what DBCC FREEPROCACHE does.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply