-- Work in progress 2013-09-30
;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, i.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.fill_factor , i.is_disabled, i.is_hypothetical, i.has_filter
FROM sys.objects o WITH(NOLOCK)
INNER JOIN sys.indexes i WITH(NOLOCK) ON i.object_id = o.object_id
LEFT JOIN sys.partitions r WITH(NOLOCK) ON r.object_id = i.object_id AND r.index_id = i.index_id
LEFT JOIN sys.dm_db_partition_stats d WITH(NOLOCK) ON d.partition_id = r.partition_id
--AND r.object_id = d.object_id AND r.index_id = d.index_id AND r.partition_number = d.partition_number
WHERE o.type <> 'TF'
-- AND row_count > 0 -- optional
), b AS (

SELECT object_id, index_id, otype, itype, 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)
, SpaC = MAX(CASE itype WHEN 4 THEN 1 ELSE 0 END)
, CoSC = MAX(CASE itype WHEN 6 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, itype, 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 AND itype=2 THEN (b.in_row_data) ELSE 0 END)
, index3 = SUM(CASE WHEN b.index_id > 1 AND itype>2 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(SpaC) SpaC , SUM(CoSC) CoSC
, 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 otype , CASE WHEN t.schema_id IS NULL THEN '' ELSE t.name END [Schema] -- , c.object_id
, CASE c.object_id WHEN 0 THEN '_Total' WHEN 1 THEN '_sys'
WHEN 2 THEN '_IT' ELSE o.name END [Table]
, [Rows], Reserved, Data, lob, ovrflw, [Index] = index2, newIx = index3, 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, SpaC, CoSC, Stct, kct, Cmpr, Part -- , Cnt
, c.data_space_id dsid , CASE y.lob_data_space_id WHEN 0 THEN NULL ELSE y.lob_data_space_id END lobds
, fif.ftct, fif.ftsz
, rkey, fkey, def, trg --, cols
, disa, hypo, filt
FROM c
LEFT JOIN sys.objects o WITH(NOLOCK) ON o.object_id = c.object_id
LEFT JOIN sys.tables y WITH(NOLOCK) ON y.object_id = c.object_id
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 table_id, SUM(data_size)/1024 ftsz , COUNT(*) ftct
 FROM sys.fulltext_index_fragments WITH(NOLOCK) WHERE [status] = 4
 GROUP BY table_id

) fif ON fif.table_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