WITH B1 AS (

 SELECT CASE WHEN d.object_id < 1000 THEN 3 ELSE d.object_id END object_id, d.index_id, COUNT(*) AS Part
  , reserved = 8*SUM(reserved_page_count), used = 8*SUM(used_page_count)
  , in_row_data = 8*SUM(in_row_data_page_count)
  , lob_used = 8*SUM(lob_used_page_count) , row_overflow_used = 8*SUM(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 AND r.object_id = d.object_id AND r.index_id = d.index_id

 GROUP BY d.object_id, d.index_id

)

SELECT x.object_id , ISNULL( o.type, 'A') AS [Type], ISNULL( t.name, 'n/a') AS [Schema]

  , CASE x.object_id WHEN 0 THEN '_Total' WHEN 3 THEN 'system' ELSE  o.name END AS [Object]

  , [Rows], Reserved, Data, [Index] = index2, Unused = Reserved - Used

  , AvBR = CASE [Rows] WHEN 0 THEN 0 ELSE 1024*[Data]/ [Rows] END

  , Clus, IxCt, Uniq, XmlC, StCnt, kct, Part, Parts, Cmpr, cols, rkey, fkey, def, trg, filt

  , CIxSk, IxSk, Scans, lkup, upd, ZrIx, mix

  , dspace, disabl, Hypo, lob, ovrflw, Ord , o.create_date --, in_row_data

FROM (

 SELECT ISNULL( i.object_id,0) object_id

 , CASE WHEN i.object_id IS NULL THEN 0 WHEN i.object_id = 3 THEN 1 ELSE 2 END AS Ord

 , Reserved = SUM(p.reserved), Used = SUM(p.used)

 , Data = SUM(CASE WHEN (p.index_id < 2) THEN (p.in_row_data + p.lob_used + p.row_overflow_used) ELSE p.lob_used + p.row_overflow_used END )

 , index2 = SUM(CASE WHEN p.index_id > 1 THEN (p.in_row_data) ELSE 0 END)

 , [Rows]  = SUM(CASE WHEN p.index_id < 2 THEN p.row_count ELSE 0 END )

 , Clus = SUM(CASE p.index_id WHEN 1 THEN 1 ELSE 0 END) 

 , IxCt = SUM(CASE WHEN i.type = 2 THEN 1 ELSE 0 END)

 , Uniq = SUM(CASE WHEN i.is_unique = 1 THEN 1 ELSE 0 END)

 , XmlC = SUM(CASE WHEN i.type = 3 THEN 1 ELSE 0 END)

 , Part  = SUM(CASE Part WHEN 1 THEN 0 ELSE 1 END), Parts = MAX(Part)

 , Cmpr = SUM(CASE compressed WHEN 0 THEN 0 ELSE 1 END)

 , dspace = COUNT(DISTINCT data_space_id)

 , disabl = SUM(CASE is_disabled WHEN 0 THEN 0 ELSE 1 END)

 , Hypo = SUM(CASE i.is_hypothetical WHEN 1 THEN 1 ELSE 0 END)

 , filt = SUM(CASE i.has_filter WHEN 1 THEN 1 ELSE 0 END)

 , in_row_data = SUM(in_row_data), lob = SUM(lob_used), ovrflw = SUM(row_overflow_used)

 FROM (

  SELECT CASE WHEN i.object_id < 1000 THEN 3 ELSE i.object_id END AS object_id  , i.index_id, i.type, i.is_unique

  , i.data_space_id, i.ignore_dup_key, i.is_primary_key, i.is_disabled, i.is_hypothetical, i.has_filter, o.type AS otype

  FROM sys.indexes i WITH(NOLOCK)

  INNER JOIN sys.objects o WITH(NOLOCK) ON o.object_id = i.object_id AND o.type NOT IN ('S', 'IT', 'TF')

  UNION ALL
  SELECT DISTINCT 3 AS object_id, i.index_id, i.type, 0, 1, 0, 0, 0, 0, 0, 'S'

  FROM sys.indexes i WITH(NOLOCK)

  INNER JOIN sys.objects o WITH(NOLOCK) ON o.object_id = i.object_id AND o.type IN ('S', 'IT', 'TF')

 ) i

 LEFT JOIN B1 p ON i.object_id = p.object_id AND i.index_id = p.index_id

 GROUP BY i.object_id

 WITH ROLLUP HAVING i.object_id IS NOT NULL OR i.object_id IS NULL

) x

LEFT JOIN sys.objects o WITH(NOLOCK) ON o.object_id = x.object_id --AND o.type NOT IN ('S', 'IT', 'TF')

LEFT JOIN sys.schemas t WITH(NOLOCK) ON t.schema_id = o.schema_id

LEFT JOIN (

 SELECT CASE WHEN objectid IS NULL THEN 0 ELSE objectid END objectid, COUNT(*) StCnt

 FROM (
  SELECT CASE WHEN object_id IS NULL THEN 0 WHEN object_id < 1000 THEN 3 ELSE object_id END objectid

  FROM sys.stats WITH(NOLOCK)
  ) y

 GROUP BY objectid

 WITH ROLLUP HAVING objectid IS NOT NULL OR objectid IS NULL ) s ON s.objectid = x.object_id

LEFT JOIN (
 SELECT CASE WHEN object_id IS NULL THEN 0 ELSE object_id END object_id, COUNT(*) cols

 FROM sys.columns WITH(NOLOCK)  GROUP BY object_id 
 WITH ROLLUP HAVING object_id IS NOT NULL OR object_id IS NULL

) c ON c.object_id = x.object_id

LEFT JOIN (
 SELECT CASE WHEN referenced_object_id IS NULL THEN 0 ELSE referenced_object_id END referenced_object_id, COUNT(*) rkey

 FROM sys.foreign_keys WITH(NOLOCK) GROUP BY referenced_object_id
 WITH ROLLUP HAVING referenced_object_id IS NOT NULL OR referenced_object_id IS NULL

) r ON r.referenced_object_id = x.object_id

LEFT JOIN (
 SELECT CASE WHEN parent_object_id IS NULL THEN 0 ELSE parent_object_id END parent_object_id, COUNT(*) fkey

 FROM sys.foreign_keys WITH(NOLOCK)  GROUP BY parent_object_id 
 WITH ROLLUP HAVING parent_object_id IS NOT NULL OR parent_object_id IS NULL

) f ON f.parent_object_id = x.object_id

LEFT JOIN ( 
 SELECT CASE WHEN parent_object_id IS NULL THEN 0 ELSE parent_object_id END parent_object_id, COUNT(*) def

 FROM sys.default_constraints WITH(NOLOCK)  GROUP BY parent_object_id 
 WITH ROLLUP HAVING parent_object_id IS NOT NULL OR parent_object_id IS NULL

) d ON d.parent_object_id = x.object_id

LEFT JOIN (
 SELECT CASE WHEN parent_id IS NULL THEN 0 ELSE parent_id END parent_id, COUNT(*) trg

 FROM sys.triggers WITH(NOLOCK) WHERE parent_id > 0 GROUP BY parent_id 
 WITH ROLLUP HAVING parent_id IS NOT NULL OR parent_id IS NULL

) g ON g.parent_id = x.object_id

LEFT JOIN (
 SELECT object_id, COUNT(*) kct FROM sys.index_columns WITH(NOLOCK) WHERE index_id = 1 GROUP BY object_id 

) k ON k.object_id = x.object_id

LEFT JOIN (

 SELECT object_id , SUM( CASE index_id WHEN 1 THEN user_seeks ELSE 0 END) CIxSk
  , SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_seeks END) IxSk
  , SUM( CASE WHEN index_id < 2 THEN user_scans ELSE 0 END) Scans
  , SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_lookups END) lkup
  , SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_updates END) upd
  , SUM( CASE WHEN index_id > 1 AND user_seeks = 0 THEN 1 ELSE user_updates END) ZrIx

 FROM sys.dm_db_index_usage_stats WITH(NOLOCK) WHERE database_id = DB_ID() GROUP BY object_id 

) l ON l.object_id = x.object_id

LEFT JOIN (

 SELECT object_id , SUM( CASE WHEN s.avg_user_impact > 90.0 THEN 1 ELSE 0 END) mix

 FROM sys.dm_db_missing_index_details d WITH(NOLOCK)

 INNER JOIN sys.dm_db_missing_index_groups g WITH(NOLOCK) ON g.index_handle = d.index_handle

 INNER JOIN sys.dm_db_missing_index_group_stats s WITH(NOLOCK) ON s.group_handle = g.index_group_handle

 WHERE d.database_id = DB_ID() GROUP BY object_id 

) m ON m.object_id = x.object_id

ORDER BY Ord, Data DESC , [Object]