Summarise indexes

Summarise indexes in the current database:

• index type & size
• index utilisation
• index & included columns.

WITH cols AS (
    SELECT 
      i.[object_id]
    , i.index_id 
    , c.[name] AS ColumnName
    , ic.key_ordinal
    , ic.is_descending_key 
    , ic.is_included_column
    FROM sys.indexes i
      INNER JOIN sys.index_columns ic 
        ON i.[object_id] = ic.[object_id] 
        AND i.index_id = ic.index_id
      INNER JOIN sys.columns c 
        ON c.[object_id] = i.[object_id] 
        AND c.column_id = ic.column_id
)
SELECT 
  s.[name] AS SchemaName
, o.[name] AS ObjectName
, o.[type] AS ObjectType
, i.[name] AS IndexName
, i.[type_desc] AS IndexType
, i.is_primary_key AS PK
, ROUND((CAST(phys.page_count AS FLOAT) * 8) / 1024, 1) AS IndexSizeMB
, COALESCE(usage.user_scans + usage.system_scans, 0) AS Scans
, COALESCE(usage.user_seeks + usage.system_seeks, 0) AS Seeks
, COALESCE(usage.user_lookups + usage.system_lookups, 0) AS Lookups
, ROUND(phys.avg_fragmentation_in_percent, 2) AS [% fragmented]
, COALESCE(SUBSTRING((
      SELECT 
        ', ' + cols.ColumnName + CASE cols.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END
      FROM cols
      WHERE cols.is_included_column = 0  
      AND cols.[object_id] = o.[object_id] 
      AND cols.index_id = i.index_id
      ORDER BY cols.key_ordinal FOR XML PATH(''), TYPE
    ).value('.','VARCHAR(MAX)') 
  , 3, 5000), '') AS IndexColumns
, COALESCE(SUBSTRING((
      SELECT 
        ', ' + cols.ColumnName
      FROM cols
      WHERE cols.is_included_column = 1
      AND cols.[object_id] = o.[object_id] AND cols.index_id = i.index_id
      ORDER BY cols.key_ordinal FOR XML PATH(''), TYPE
    ).value('.','VARCHAR(MAX)') 
  , 3, 5000), '') AS IncludedColumns
FROM sys.indexes i
  INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]
  INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
  LEFT JOIN sys.dm_db_index_usage_stats usage
    ON usage.[object_id] = o.[object_id]
    AND usage.index_id = i.index_id
    AND usage.database_id = DB_ID()
  LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) phys
    ON phys.[object_id] = o.[object_id]
    AND phys.index_id = i.index_id
    AND phys.alloc_unit_type_desc = 'IN_ROW_DATA'
WHERE i.[type] > 0 -- != heap
AND o.[type] IN ('U', 'V')
ORDER BY
  SchemaName
, ObjectName
, IndexName