USE: To display index information.

SET NOCOUNT ON

SELECT DB_NAME(IPS.database_id) AS [database_name]
     , OBJECT_NAME(IPS.object_id, IPS.database_id) AS [object_name]
     , QUOTENAME(I.name) [index_name]
     , CASE WHEN avg_fragmentation_in_percent < 10 THEN 'LOW'
            WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM'
            WHEN avg_fragmentation_in_percent < 50 THEN 'HIGH'
            ELSE 'EXTREME' END [fragmentation_indicator]
     , IPS.index_id
     , IPS.index_type_desc
     , IPS.avg_fragmentation_in_percent
     , IPS.fragment_count
     , IPS.avg_fragment_size_in_pages
     , IPS.page_count
  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS IPS
 INNER JOIN sys.indexes I  ON IPS.object_id = I.object_id 
   AND IPS.index_id = I.index_id
 ORDER BY [object_name], [index_name]
GO