Find Index fragmentation for each index on a table in a DB

The following query will give out the fragmentation percentage for each index on a table in DB.


SELECT DISTINCT * FROM( SELECT  DB_NAME(ps.database_id) AS DBName
,OBJECT_NAME(ps.OBJECT_ID) AS TabeName
,ps.index_id
,b.name
,b.type_desc 
,ps.avg_fragmentation_in_percent
      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
     INNER JOIN sys.indexes AS b 
     ON ps.OBJECT_ID = b.OBJECT_ID
    AND ps.index_id         =  b.index_id
    WHERE ps.database_id = DB_ID()
          )A
ORDER BY a.avg_fragmentation_in_percent DESC