sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information.but query returns lots of information.
SELECT OBJECT_NAME(idx.OBJECT_ID) AS TableName,
idx.name AS IndexName,idxst.avg_fragmentation_in_percent,
idxst.avg_fragment_size_in_pages,
idxst.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') idxst
INNER JOIN sys.indexes idx ON idx.OBJECT_ID = idxst.OBJECT_ID
AND idx.index_id = idxst.index_id
WHERE idxst.avg_fragmentation_in_percent > 5
Decide to de-fragment an index we should determine how much affected it is. Fragmentation is measured as a percentage that indicates the number of pages that are not in the ideal order. A value below 5% indicates a very low fragmented index that requires no maintenance. A value of between 5% and 30% can be improved by reorganizing the idex. Greater than 30% fragmentation warrants an index rebuild.
