WITH b AS (

 SELECT d.object_id, d.index_id, COUNT(*) AS Part, reserved = 8*SUM( d.reserved_page_count) , used = 8*SUM( d.used_page_count )

  , in_row_data = 8*SUM( d.in_row_data_page_count), lob_used = 8*SUM( d.lob_used_page_count), overflow = 8*SUM( d.row_overflow_used_page_count)

  , row_count = SUM(row_count), compressed = SUM(data_compression) -- change to 0 for SQL Server 2005

 FROM sys.dm_db_partition_stats d WITH(NOLOCK)  INNER JOIN sys.partitions r WITH(NOLOCK) ON r.partition_id = d.partition_id

 GROUP BY d.object_id, d.index_id

)

SELECT o.type, o.name [table], i.name [index]

, p.[1] +COALESCE(', ' +p.[2] +COALESCE(', ' +p.[3] +COALESCE(', ' +p.[4] +COALESCE(', ' +p.[5] +COALESCE(', ' +p.[6] +COALESCE(', ' +p.[7]

 +COALESCE(', ' +p.[8] +CASE WHEN p.[9] IS NULL THEN '' ELSE p.[9] + ' + more keys?' END, ''), ''), ''), ''), ''), ''), '') AS keys

, COALESCE('INCLUDE (' +q.[1] +COALESCE(', ' +q.[2] +COALESCE(', ' +q.[3] +COALESCE(', ' +q.[4] +COALESCE(', ' +q.[5] +COALESCE(', ' +q.[6]

 +COALESCE(', ' +q.[7] +COALESCE(', ' +q.[8] +COALESCE(', ' +q.[9]

 +CASE WHEN q.[10] IS NULL THEN '' ELSE q.[10] + ' + more?' END , ''), ''), ''), ''), ''), ''), ''), '') +')', '') AS includ

, CASE WHEN p.[1] IS NULL THEN 0 ELSE 1 END + CASE WHEN p.[2] IS NULL THEN 0 ELSE 1 END + CASE WHEN p.[3] IS NULL THEN 0 ELSE 1 END

+ CASE WHEN p.[4] IS NULL THEN 0 ELSE 1 END AS kct

, i.index_id/*, i.type_desc*/ , i.is_unique

, b.Part, b.compressed, b.reserved, b.in_row_data, b.overflow, b.row_count

, CASE b.row_count WHEN 0 THEN 0 ELSE 1024*b.used/b.row_count END AS ABR

, x.user_seeks, x.user_scans, x.user_lookups, x.user_updates

, x.last_user_seek, x.last_user_scan, x.last_user_lookup, x.last_user_update

, i.data_space_id, i.is_primary_key, i.is_unique_constraint, i.has_filter

FROM sys.objects o

INNER JOIN sys.indexes i ON i.object_id = o.object_id

INNER JOIN b ON b.object_id = i.object_id AND b.index_id = i.index_id

LEFT JOIN (
  SELECT j.object_id, j.index_id, j.key_ordinal, c.name

  FROM sys.index_columns j INNER JOIN sys.columns c ON c.object_id = j.object_id AND c.column_id = j.column_id

  WHERE j.is_included_column = 0 ) AS S

 PIVOT ( MAX(name) FOR key_ordinal IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS p ON p.object_id = i.object_id AND p.index_id = i.index_id

LEFT JOIN (
  SELECT j.object_id, j.index_id, c.name, ROW_NUMBER() OVER ( PARTITION BY j.object_id, j.index_id ORDER BY j.index_column_id) AS rn

  FROM sys.index_columns j INNER JOIN sys.columns c ON c.object_id = j.object_id AND c.column_id = j.column_id

  WHERE j.is_included_column = 1 AND j.key_ordinal = 0 ) AS S

 PIVOT ( MAX(name) FOR rn IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS q ON q.object_id = i.object_id AND q.index_id = i.index_id

LEFT JOIN sys.dm_db_index_usage_stats x ON x.object_id = i.object_id AND x.index_id = i.index_id AND x.database_id = DB_ID()

WHERE o.type IN ('U', 'V')

ORDER BY o.name, i.index_id