USE [DBACHECKDB] GO /****** Object: Table [dbo].[RecompiledSPDescription] Script Date: 16.01.2017 13:40:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RecompiledSPDescription]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Dbname] [nvarchar](128) NULL, [RecompiledSPDescription] [nvarchar](1000) NULL, [IsRecompiled] [bit] NULL CONSTRAINT [DF_RecompiledSPDescription_IsRecompiled] DEFAULT ((0)), [LogDate] [date] NOT NULL CONSTRAINT [DF_DBNameAndStoredProcedures_LogDate] DEFAULT (getdate()), CONSTRAINT [PK_DBNameAndStoredProcedures] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO IF OBJECT_ID('tempdb..##DBNameAndStoredProcedures','U') IS NOT NULL DROP TABLE ##DBNameAndStoredProcedures TRUNCATE TABLE DBACHECKDB.dbo.RecompiledSPDescription CREATE TABLE ##DBNameAndStoredProcedures (Dbname nvarchar(20) ,SpName nvarchar(100) ) DECLARE @dbname VARCHAR(1000) DECLARE @ProcedueName VARCHAR(100) DECLARE @CommandToExecute VARCHAR(4000) DECLARE DBNameAndStoredProcedures_Cur CURSOR FAST_FORWARD FOR SELECT name FROM sys.databases WHERE name NOT IN('tempdb','model') AND is_read_only=0 AND state_desc='online' ORDER BY name OPEN DBNameAndStoredProcedures_Cur; FETCH NEXT FROM DBNameAndStoredProcedures_Cur INTO @dbname WHILE @@FETCH_STATUS=0 BEGIN SET @CommandToExecute= +CHAR(13)+CHAR(10) +N'USE ['+@dbname+'] ' +CHAR(13)+CHAR(10) +N'INSERT INTO ##DBNameAndStoredProcedures (DbName,SpName)' +CHAR(13)+CHAR(10) +N'SELECT DB_NAME() as Dbname, S.name+''.''+P.Name as SpName' +CHAR(13)+CHAR(10) +N' FROM sys.procedures P' +CHAR(13)+CHAR(10) +N'INNER JOIN sys.schemas S ON P.schema_id = S.schema_id' +CHAR(13)+CHAR(10) +N'INSERT INTO [DBACHECKDB].[dbo].[RecompiledSPDescription] (DbName, RecompiledSPDescription)' +CHAR(13)+CHAR(10) +N'SELECT DbName, ''USE ''+Dbname+'' EXECUTE sp_recompile N''''''+SpName+'''''''' FROM ##DBNameAndStoredProcedures' +CHAR(13)+CHAR(10) FETCH NEXT FROM DBNameAndStoredProcedures_Cur INTO @dbname EXECUTE (@CommandToExecute) --PRINT(@CommandToExecute) END CLOSE DBNameAndStoredProcedures_Cur; DEALLOCATE DBNameAndStoredProcedures_Cur; -- ikinci kısım, yukarıda tablo içerisine insert ettiğimiz sp isimlerini execute ettireceğiz. DECLARE @description NVARCHAR(2000) ,@ID int DECLARE sprecompile_cur CURSOR FAST_FORWARD FOR SELECT ID,[RecompiledSPDescription] FROM [DBACHECKDB].[dbo].[RecompiledSPDescription] OPEN sprecompile_cur; FETCH NEXT FROM sprecompile_cur INTO @ID,@description WHILE @@FETCH_STATUS=0 BEGIN EXECUTE (@description) --PRINT (@description) UPDATE [DBACHECKDB].[dbo].[RecompiledSPDescription] SET IsRecompiled = 1 WHERE ID=@ID FETCH NEXT FROM sprecompile_cur INTO @ID,@description END CLOSE sprecompile_cur; DEALLOCATE sprecompile_cur;
