SQLServer : ユーザ定義のインデックスを再編成

ユーザ定義されたインデックスを調べて再編成してくれるTrasact-SQL。断片率が10%超の時に再編する。

DECLARE @INDEX_NAME sysname
DECLARE @TABLE_NAME sysname
DECLARE @SQL nvarchar(max)
DECLARE @TEMPLATE_SQL nvarchar(max)

SET @TEMPLATE_SQL = 'ALTER INDEX @1 On @2 REORGANIZ'

DECLARE CUR CURSOR FOR
SELECT OBJECT_NAME(a.object_id) AS TableName, name
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS a
 JOIN sys.indexes AS b
 ON a.object_id = b.object_id AND a.index_id = b.index_id
 WHERE a.index_id > 0 AND avg_fragmentation_in_percent > 10

OPEN CUR
FETCH NEXT FROM CUR INTO @TABLE_NAME, @INDEX_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = REPLACE(@TEMPLATE_SQL, '@1', @INDEX_NAME)
    SET @SQL = REPLACE(@SQL, '@2', @TABLE_NAME)
    PRINT @SQL
    FETCH NEXT FROM CUR
    INTO @TABLE_NAME, @INDEX_NAME
END

CLOSE CUR
DEALLOCATE CUR