Technical Article

Recompile All Databases stored Procedures

,

this iteration of the steps will recompile( or more correctly mark for recompilation ) all stored procedures in all the databases.

You could easliy add an AND and and further filtering to only work with just the objects you are needing to recompile.

I have had a few issues where I renamed and database and the execution plans were pointing to the old database and it was trying to use and offline database. So I created this. Once I ran this there were no more issues and all the procs execution plans were fresh.

Thanks for looking. 

-----
/*
Recompile Database Stored Proedures

*/ --------
PRINT @@ServerName;
DECLARE @name sysname;
 -- database name
DECLARE @dbname sysname;
 -- database name
DECLARE @cmd NVARCHAR(4000);
DECLARE @cmd2 NVARCHAR(4000);  
DECLARE @SQL VARCHAR(MAX); 
DECLARE @TblTable TABLE
  (
DBName sysname
  , RecompileStmt NVARCHAR(4000)
  );
IF OBJECT_ID('tempdb..#ProcTable') IS NOT NULL
  BEGIN
DROP TABLE #ProcTable;
  END;
CREATE TABLE #ProcTable
  (
RecompileStmt NVARCHAR(4000)
  );
--
-- Filter out the databases you wish to operate on
--
DECLARE db_cursor CURSOR
FOR
  SELECT
  name
FROM
  master.dbo.sysdatabases
WHERE
  CONVERT(VARCHAR(20), DATABASEPROPERTYEX(name, 'Status')) = 'ONLINE'
  AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb' );


OPEN db_cursor;   
FETCH NEXT FROM db_cursor INTO @name;   
--
-- Get items from list/table ofStored Procedures to recompile fiters could be applied here as well
--
WHILE @@Fetch_Status = 0
  BEGIN   

INSERT INTO @TblTable
(
  DBName
, RecompileStmt
)
  VALUES
(
  @name
, N'Select N' + CHAR(39) + 'USE [' + @name
  + '] EXEC sp_recompile [' + CHAR(39)
  + '+SPECIFIC_NAME+' + CHAR(39) + ']' + CHAR(39)
  + ' from [' + @name
  + '].information_schema.routines where routine_type = '
  + CHAR(39) + 'PROCEDURE' + CHAR(39)
); 


FETCH NEXT FROM db_cursor INTO @name;  
  -- 
  END; 
CLOSE db_cursor;   
DEALLOCATE db_cursor;
--
-- Execute all the recompiles that were created above.
-- 
DECLARE proc_cursor CURSOR
FOR
  SELECT
  DBName
, RecompileStmt
FROM
  @TblTable; 

OPEN proc_cursor;   
FETCH NEXT FROM proc_cursor INTO @dbname, @cmd;   

WHILE @@Fetch_Status = 0
  BEGIN   
PRINT @cmd; 
INSERT INTO #ProcTable
( RecompileStmt )
EXEC sys.sp_executesql @cmd;

DECLARE Recompile_cursor CURSOR
FOR
  SELECT
  RecompileStmt
FROM
  #ProcTable; 

OPEN Recompile_cursor;   
FETCH NEXT FROM Recompile_cursor INTO @cmd2;   

WHILE @@Fetch_Status = 0
  BEGIN   
PRINT @cmd2;
EXEC sys.sp_executesql @cmd2;
FETCH NEXT FROM Recompile_cursor INTO @cmd2;   
--
  END; 
CLOSE Recompile_cursor;   
DEALLOCATE Recompile_cursor;


FETCH NEXT FROM proc_cursor INTO @dbname, @cmd;   
   --
  END; 
CLOSE proc_cursor;   
DEALLOCATE proc_cursor;
--
--
PRINT 'Completed --->'

Rate

2.5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

2.5 (4)

You rated this post out of 5. Change rating