In this Article, I am sharing T-SQL Script to find the Index Fragmentation report for SQL Server User database.
| USE DatabaseName –Need to Specify the User Database Name go SELECT OBJECT_NAME(fq.OBJECT_ID) AS TableName, fq.name AS IndexName, qs.index_type_desc AS IndexType, qs.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) qs INNER JOIN sys.indexes fq ON fq.object_id = qs.object_id AND fq.index_id = qs.index_id WHERE qs.avg_fragmentation_in_percent > 0 ORDER BY qs.avg_fragmentation_in_percent DESC |
After you run the script, you will get the Below result.
