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(DBName, RecompileStmt)VALUES(@name, 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.
(From https://msdn.microsoft.com/en-us/library/ms190439(v=sql.110).aspx)
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