SQLServer インデックスデフラグをストアド化

— 管理テーブル変数
DECLARE @DeflagSetTable table([ExecFlag] [tinint] ,[ExecSort] [tinyint] ,TblName varchar(255) ,IdxName varchar(255) ,[ExecStartDateTime] [datetime] ,[ExecEndDateTime] [datetime] ,[ExecStatus] varchar(50))
— 管理データ取得用テーブル変数
DECLARE @DeflagExecTable table(TblName varchar(255) ,IdxName varchar(255))
— エラー管理変数
DECLARE @IntStepCode int
DECLARE @IntErrorCode int

— エラー取得
SELECT @IntErrorCode = @@ERROR

——————————————–
— 管理テーブル作成
——————————————–
SET @IntStepCode = 1

SET @IntErrorCode = 0

— インデックス管理テーブルが無い場合は、作成する
IF (SELECT count( * ) FROM dbo.sysobjects where name = ‘CNT_INDEX_DEFRAG’) = 0
BEGIN
— テーブル定義
— ExecFlag 実行フラグ（0:初期値(default) 1:デフラグ実行 2:デフラグ不要）
— TblName テーブル名
— IdxName インデックス名
— ExecStartDateTime 処理開始日時
— ExecStartDateTime 処理終了日時
— ExecStatus 状態（未実行 実行中 実行済）
CREATE TABLE [dbo].[CNT_INDEX_DEFRAG] ([ExecFlag] [tinyint] NULL ,[ExecSort] [tinyint] NULL ,[TblName] [varchar] (255) NOT NULL ,[IdxName] [varchar] (255) NOT NULL ,[ExecStartDateTime] [datetime] NOT NULL ,[ExecEndDateTime] [datetime] NOT NULL , [ExecStatus] varchar(50) NULL ) ON [PRIMARY]
ALTER TABLE dbo.CNT_INDEX_DEFRAG ADD CONSTRAINT DF_CNT_INDEX_DEFRAG_ExecFlag DEFAULT (0) FOR ExecFlag

— エラー取得
SELECT @IntErrorCode = @@ERROR

END

——————————————–
— インデックス情報挿入
——————————————–
SET @IntStepCode = 2

IF @IntErrorCode = 0
— インデックス管理テーブルへデータ挿入①
INSERT INTO @DeflagSetTable
SELECT 0, 0, sysobjects.name AS TblName, sysindexes.name AS IdxName, getdate(), getdate(), ‘未実行’
FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE (sysobjects.name <> N’dtproperties’) AND (sysobjects.xtype = ‘U’) AND (LEFT(sysindexes.name, 8) <> ‘_WA_Sys_’) AND (sysobjects.name <> sysindexes.name)

— インデックス管理テーブルへデータ挿入②
IF (SELECT count( * ) FROM CNT_INDEX_DEFRAG) = 0
BEGIN
INSERT INTO CNT_INDEX_DEFRAG SELECT * FROM @DeflagSetTable

— エラー取得
SELECT @IntErrorCode = @@ERROR
END

——————————————–
— インデックス再編成処理
——————————————–
SET @IntStepCode = 3

IF @IntErrorCode = 0
BEGIN
DECLARE @TblName varchar(255)
DECLARE @IdxName varchar(255)
DECLARE @BkTblName varchar(255)

IF (SELECT count(*) FROM CNT_INDEX_DEFRAG WHERE (ExecFlag = 1) AND (ExecStatus <> ‘実行済’)) > 0
— 残件実行（復旧対応用SQL）
INSERT INTO @DeflagExecTable SELECT TblName, IdxName FROM CNT_INDEX_DEFRAG WHERE (ExecFlag = 1) AND (ExecStatus <> ‘実行済’) ORDER BY ExecSort
ELSE
BEGIN
— 状態初期化
UPDATE CNT_INDEX_DEFRAG SET ExecStatus = ‘未実行’ WHERE (ExecFlag = 1)
— 全件実行
INSERT INTO @DeflagExecTable SELECT TblName, IdxName FROM CNT_INDEX_DEFRAG WHERE (ExecFlag = 1) ORDER BY ExecSort
END

INSERT INTO @DeflagExecTable SELECT TblName, IdxName FROM CNT_INDEX_DEFRAG WHERE (ExecFlag = 1)

DECLARE deflag cursor for select * FROM @DeflagExecTable

OPEN deflag
FETCH next FROM deflag
INTO @TblName, @IdxName

— インデックスデフラグ処理ループ開始
WHILE @@fetch_status = 0
BEGIN
— インデックス管理テーブルの開始日時更新
UPDATE CNT_INDEX_DEFRAG set
ExecStartDateTime = getdate(),
ExecStatus = ‘実行中’
WHERE (TblName = @TblName) and (IdxName = @IdxName)

— インデックスデフラグ処理実行
DBCC INDEXDEFRAG (0, @TblName, @IdxName)

— エラー取得
SELECT @IntErrorCode = @@ERROR
IF @IntErrorCode > 0
— インデックス管理テーブルにエラーNoを更新
UPDATE CNT_INDEX_DEFRAG set
ExecStatus = ‘エラー:’ + CAST( @IntErrorCode AS varchar(20) )
WHERE (TblName = @TblName) and (IdxName = @IdxName)

— インデックス管理テーブルの完了日時更新
UPDATE CNT_INDEX_DEFRAG set
ExecEndDateTime = getdate(),
ExecStatus = ‘実行済’
WHERE (TblName = @TblName) and (IdxName = @IdxName)

— テーブル名をバックアップ
SET @BkTblName = @TblName

FETCH next FROM deflag
INTO @TblName, @IdxName
END
— インデックスデフラグ処理ループ終了
CLOSE deflag
END

———- エラー制御
BEGIN
IF @IntErrorCode > 0
BEGIN

IF @IntStepCode = 1
Print ‘管理テーブル作成に失敗しました。’

IF @IntStepCode = 2
Print ‘インデックス情報挿入に失敗しました。’

IF @IntStepCode = 3
Print ‘インデックス再編成処理に失敗しました。’

Return @IntStepCode
END
ELSE
Return 0

END
GO