This code snippets can be used to find out the fragmentation details of index in database.
Code Snippets:
- SELECT IPS.database_id AS Database_Id, IPS.OBJECT_ID,
- IPS.index_id, Ind.name AS Key_Name,
- IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages
- FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS IPS
- INNER JOIN sys.indexes AS Ind ON IPS.OBJECT_ID = Ind.OBJECT_ID
- AND IPS.index_id = Ind.index_id
- WHERE IPS.database_id = DB_ID()
- ORDER BY IPS.OBJECT_ID