SELECT TOP 100
OBJECT_NAME(ps.object_id) AS TableName
,i.name AS IndexName
,ips.index_type_desc
,index_level
,ips.avg_fragmentation_in_percent
,ips.avg_page_space_used_in_percent
,ips.page_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'DETAILED') ips
ORDER BY ips.avg_fragmentation_in_percent DESC