-- Work in progress 2012-04-01

;WITH a AS (

SELECT CASE WHEN o.schema_id = 4 THEN CASE WHEN o.type = 'S' THEN 1

WHEN o.type = 'IT' THEN 2 ELSE 3 END ELSE o.object_id END AS object_id, o.type AS otype

, d.index_id, CASE o.type WHEN 'IT' THEN 1 ELSE i.data_space_id END data_space_id

, d.reserved_page_count, d.used_page_count

, d.in_row_data_page_count, d.lob_used_page_count, d.row_overflow_used_page_count, d.row_count

, r.data_compression, r.partition_number, i.type itype, i.is_unique

, i.is_disabled, i.is_hypothetical, i.has_filter

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.partition_number = d.partition_number

  --AND r.object_id = d.object_id AND r.index_id = d.index_id

INNER JOIN sys.indexes i WITH(NOLOCK) ON i.object_id = d.object_id AND i.index_id = d.index_id

INNER JOIN sys.objects o WITH(NOLOCK) ON o.object_id = d.object_id

-- WHERE row_count > 0 -- optional

), b AS (

SELECT object_id, index_id, otype , data_space_id

, CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END Part, COUNT(*) AS Cnt

, 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

, Clus = MAX(CASE a.index_id WHEN 1 THEN 1 ELSE 0 END)

, IxCt = MAX(CASE itype WHEN 2 THEN 1 ELSE 0 END)

, XmlC = MAX(CASE itype WHEN 3 THEN 1 ELSE 0 END)

, Uniq = MAX(CASE is_unique WHEN 1 THEN 1 ELSE 0 END)

, disa = MAX(CASE is_disabled WHEN 1 THEN 1 ELSE 0 END)

, hypo = MAX(CASE is_hypothetical WHEN 1 THEN 1 ELSE 0 END)

, filt = MAX(CASE has_filter WHEN 1 THEN 1 ELSE 0 END)

FROM a GROUP BY object_id, index_id, otype , data_space_id

), c AS (

SELECT CASE WHEN otype IS NULL THEN 'A' ELSE otype END otype

, CASE WHEN b.object_id IS NULL THEN 0 ELSE b.object_id END AS object_id

, CASE WHEN b.object_id IS NULL THEN 0 WHEN b.object_id IN (1,2) THEN b.object_id ELSE 3 END Ord , data_space_id

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

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

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

  ELSE b.lob_used + b.row_overflow_used END )

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

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

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

, SUM(CASE WHEN b.object_id > 10 AND Part > 0 THEN 1 ELSE 0 END) AS Part

, MAX(CASE WHEN b.object_id < 10 THEN Cnt ELSE 0 END) AS Cnt

, SUM(Clus) Clus, SUM(IxCt) IxCt , SUM(XmlC) XmlC, SUM(Uniq) Uniq

, SUM(disa) disa, SUM(hypo) hypo, SUM(filt) filt

FROM b

GROUP BY b.object_id, data_space_id, otype

WITH ROLLUP HAVING (b.object_id IS NOT NULL AND otype IS NOT NULL AND data_space_id IS NOT NULL ) OR b.object_id IS NULL

)

SELECT CASE WHEN t.schema_id IS NULL THEN '' ELSE t.name END [Schema]

, CASE c.object_id WHEN 0 THEN '_Total' WHEN 1 THEN '_sys' WHEN 2 THEN '_IT' ELSE o.name END [Table]

, c.data_space_id dsid /*, c.object_id*/, otype

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

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

, CASE WHEN c.object_id IN (1,2,3) THEN Cnt ELSE Clus END Clus, IxCt, Uniq, XmlC

, Stct, kct, Part , Cmpr, cols/*, Cnt*/

, rkey, fkey, def, trg, disa, hypo, filt, o.create_date

FROM c

LEFT JOIN sys.objects o WITH(NOLOCK) ON o.object_id = c.object_id --AND c.object_id > 10 -- don't join the lower values

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

LEFT JOIN (

 SELECT CASE WHEN object_id IS NULL THEN 0 ELSE object_id END object_id, COUNT(*) Stct

 FROM sys.stats WITH(NOLOCK) WHERE object_id > 3 -- skip low values

 GROUP BY object_id WITH ROLLUP HAVING object_id IS NOT NULL OR object_id IS NULL

) s ON s.object_id = c.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 = c.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*/

) e ON e.object_id = c.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 = c.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 = c.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 = c.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 = c.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 = c.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 = c.object_id

*/

ORDER BY Ord, Reserved DESC