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