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

ユーザ定義されたインデックスを調べて再構築してくれるTrasact-SQL。

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 REBUILD with (FILLFACTOR = 10)'

DECLARE CUR CURSOR FOR
SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName FROM sys.indexes WHERE OBJECT_SCHEMA_NAME (object_id) <> 'sys' AND index_id > 0 ORDER BY 1

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