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