space used summary for tables

/*
** Copyright Joe Chang, Elemental Inc. 2003 - 2006
** this is the script i use to get summary info on table size etc
** i recall that i started with the code for sp-spaceused,
** set it to list all tables,
** then i added stuff like average row size, has clustered index, number of nonclustered ** indexes, number of stats (not on indexes), max index depth, the rowmodctr from
** sysindexes,

** i suppose i could add whether there are text/image entries
*/

set nocount on

if exists (select * from tempdb..sysobjects where name LIKE '#spt_space%' and xtype = 'U')
drop table #spt_space

create table #spt_space (
idint,
uidsmallint,
namevarchar(255),
rowsbigint null,
reservedfloat null,
datafloat null,
indexpfloat null,
unusedfloat null,
Clust int,
IX_Cnt int,
Stat_Cntint,
IX_Depthint,
RowModint
)
GO
insert into #spt_space (id, uid, name, rows, reserved, data, indexp, Clust,IX_Cnt,Stat_Cnt, IX_Depth, RowMod)
select o.id, o.uid, o.name,
rows = (select rows from sysindexes i WITH (NOLOCK) where i.id = o.id and indid < 2)
,reserved = (select sum(reserved) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid in (0, 1, 255))
,data = (select sum(dpages) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid < 2) +
(select isnull(sum(used), 0) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid =255)
,indexp = (select sum(used) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid in (0, 1, 255))
,Clust = CASE WHEN (select count(*) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid =1)=1 THEN 1 ELSE 0 END
,IX_Cnt = (select count(*) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid BETWEEN 2 AND 254 AND status&64 =0)
,Stat_Cnt = (select count(*) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid BETWEEN 2 AND 254 AND status&64 =64)
,IX_Depth = (select ISNULL(MAX(indexProperty(id, i.name, 'indexDepth')),0) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid BETWEEN 1 AND 254 AND status&64 =0)
,RowMod = (select sum(rowmodctr) from sysindexes i WITH (NOLOCK) where i.id = o.id and indid in (0, 1))
from sysobjects o WITH (NOLOCK)
where xtype IN ('U') -- add 'S' for system tables
GO
update #spt_space set unused = reserved - indexp
GO
update #spt_space set indexp = indexp - data
GO
select --[id] = 0,
[User] = 'User'
, [Table] = ' Total'
, [rows] = SUM(rows)
, [reserved KB] = CONVERT(bigint,SUM(reserved * d.low / 1024.))
, [data KB] = CONVERT(bigint,SUM(data * d.low / 1024.))
, [index KB]= CONVERT(bigint,SUM(indexp * d.low / 1024.))
, [unused] = CONVERT(bigint,SUM(unused * d.low / 1024.))
, [A.RowSz]= str(SUM(data * d.low)/SUM(rows),8,1)
, [Clust] = SUM(Clust)
, [IX's] = SUM(IX_Cnt)
, [Stat's] = SUM(Stat_Cnt)
, [IX_Depth] = 0
--, [dpages] = CONVERT(int,SUM(data))
, [RowMod] = 0
from #spt_space, master.dbo.spt_values d
where d.number = 1 and d.type = 'E' --AND rows > 0
UNION ALL
select --[id],
[User] = USER_NAME(uid)
, [Table] = substring(#spt_space.name, 1, 40)
, [rows] = rows
, [reserved KB]= CONVERT(bigint,reserved * d.low / 1024.)
, [data KB]= CONVERT(bigint,data * d.low / 1024.0)
, [index KB] = CONVERT(bigint,indexp * d.low / 1024.)
, [unused] = CONVERT(bigint,unused * d.low / 1024.)
, [A.RowSz] = str((d.low*data)/(rows+1),8,1)
, [Clust] = Clust
, [IX's] = IX_Cnt
, [Stat's] = Stat_Cnt
, [IX_Depth]= IX_Depth
--, [dpages] = convert(int,data)
, [RowMod]
from #spt_space, master.dbo.spt_values d
where d.number = 1 and d.type = 'E' --AND rows > 0
order by [reserved KB] DESC
--order by rows DESC
--order by [Table] --#spt_space.nam e
GO

drop table #spt_space
GO