SELECT
DB_NAME() AS [Database Name],
QUOTENAME(o.name) AS [Table Name],
stats.index_id AS [Index ID],
i.name AS [Index Name],
i.type_desc AS [Index Type],
CAST(stats.avg_fragmentation_in_percent AS DECIMAL (38,2)) AS [Average Fragmentation (%)],
stats.page_count [Page Count],
CASE
when stats.avg_fragmentation_in_percent < 30 then 'Reorganize'
when stats.avg_fragmentation_in_percent > 30 then 'Rebuild'
END AS [Comments]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') AS stats,sys.objects AS o,sys.schemas AS s, sys.indexes AS i
WHERE
o.object_id = stats.object_id AND
s.schema_id = o.schema_id AND
i.object_id = stats.object_id AND
i.index_id = stats.index_id AND
stats.avg_fragmentation_in_percent >= 10.0 AND
stats.page_count >= 5000 AND
stats.index_id > 0
ORDER BY [Average Fragmentation (%)] DESC,[Page Count] DESC, [Comments]