sp_toptables

CREATE proc sp_toptables @topcount int = 10
as
declare@pagesize bigint
declare @dbid int

select@pagesize = low 
frommaster.dbo.spt_values
wherenumber = 1
andtype = 'E'

declare @spt_space table
(
objidint null,
rowsint null,
reserved int null,
dataint null,
indexpint null,
unusedint null
)

insertinto @spt_space
selectobjid = id, 
rows = sum(case when indid in (0, 1) then rowcnt else 0 end),
reserved = sum(case when indid in (0, 1, 255)
then reserved
else 0
end) * @pagesize / 1024,
data = sum(case when indid in (0, 1) then dpages
when indid = 255 then used
else 0
end) * @pagesize / 1024,
indexp = sum(case when indid in (0, 1, 255)
then used
else 0
end) * @pagesize / 1024,
unused = sum(case when indid in (0, 1, 255)
then used
else 0
end) * @pagesize / 1024
fromsysindexes
whererowcnt > 0
group
byid, indid
order 
byreserved desc

set rowcount @topcount
selectTable_Name = name,
rows,
reserved_KB = ltrim(str(reserved,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp - data,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(reserved - unused,15,0) + ' ' + 'KB'),
idx_data_ratio = ltrim(str((indexp - data)*100 /data) + '%'),
unused_pct = ltrim(str((reserved - unused) * 100 /reserved) + '%')
from@spt_space s join sysobjects o on o.id = s.objid and xtype = 'U'
wheredata > 0
order 
byreserved desc
set rowcount 0