查詢應rebuild的index清單與語法

DECLARE @NeedReorganize int, @NeedReBuild int
--設定需要Reorg的值定為：10，需Rebuild的值為：15
SELECT @NeedReorganize = 10, @NeedReBuild = 15
--
SELECT ix.object_id as ObjectID, s.name as SchemaName, t.name as TableName, ix.name as IndexName
, ps.avg_fragmentation_in_percent, ps.partition_number, pc.partition_count
, (CASE 
WHEN ps.avg_fragmentation_in_percent > @NeedReorganize THEN 
('ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ') + 
(CASE WHEN ps.avg_fragmentation_in_percent > @NeedReBuild THEN 'REBUILD' ELSE 'REORGANIZE' END) + 
(CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(max)) ELSE '' END)
ELSE ''
END) as Command
FROM sys.indexes AS ix
INNER JOIN sys.tables t ON t.object_id = ix.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN (
SELECT object_id,index_id,avg_fragmentation_in_percent,partition_number
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
) ps ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (
SELECT object_id,index_id,COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions GROUP BY object_id,index_id
) pc ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ix.name is not null
AND   avg_fragmentation_in_percent > 10   --配合設定的值顯示僅需要維護的清單