*** 指定したDB内で、30%以上断片化しているINDEXを再構築するクエリ [#qeb99ba7]
declare @databaseName nvarchar(255)
set @databaseName = 'LOKMSTIDX'
EXEC ('USE ' + @databaseName)
-- 処理前
SELECT c.name as tablename
,b.name as indexname
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.objects AS c
ON a.object_id = c.object_id
AND is_ms_shipped = 0
GO
DECLARE @indexname varchar(255)
,@tablename varchar(255)
DECLARE fragmented_index_cursor CURSOR FOR
SELECT b.name as indexname
,c.name as tablename
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.objects AS c
ON a.object_id = c.object_id
AND is_ms_shipped = 0
WHERE avg_fragmentation_in_percent > 30
OPEN fragmented_index_cursor
FETCH NEXT FROM fragmented_index_cursor
INTO @indexname, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '@indexname : ' + @indexname + ' @tablename : ' + @tablename
EXEC ('ALTER INDEX ' + @indexname
+ ' ON ' + @databaseName + '..' + @tablename + ' REBUILD')
FETCH NEXT FROM fragmented_index_cursor
INTO @indexname, @tablename
END
CLOSE fragmented_index_cursor
DEALLOCATE fragmented_index_cursor
GO
-- 処理後
SELECT c.name as tablename
,b.name as indexname
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, NULL) AS a
INNER JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.objects AS c
ON a.object_id = c.object_id
AND is_ms_shipped = 0
GO