this is a repost of something i wrote a long time ago. there are already stored procs to view table sizes on a specific table. this was just something that i wrote so that i could create a list of all of the tables in the current database and change the sort orders if i wanted. it also gave me the ability to filter based on table sizes if i needed to only see tables in a certain range.
sorry the code formatting is lame. blame the new version of community server.
-- view all
table sizes
-- author:
royashbrook - royashbrook@yahoo.com
-- april 2005
select
so.id as
[OBJECT_ID],
so.name as [OBJECT_NAME],
coalesce(j_rows.rows,0) as [ROWCOUNT],
coalesce(j_ru.sum_reserved,0) * cast(m.low as dec) / 1024 as [RESERVED (KB)],
d.data *
cast(m.low as dec) / 1024 as [DATA (KB)],
(coalesce(j_ru.sum_used,0) - d.data) * cast(m.low as dec) / 1024 as [INDEX
(KB)],
(coalesce(j_ru.sum_reserved,0) - coalesce(j_ru.sum_used,0)) * cast(m.low as dec) / 1024 as [UNUSED (KB)]
from
sysobjects so
join master.dbo.spt_values m
on m.number = 1 and m.type = 'E'
-- rows
left join
sysindexes j_rows
on j_rows.indid < 2 and j_rows.id = so.id
-- reserved: sum(reserved) where
indid in (0, 1, 255)
-- index: sum(used) where indid in
(0, 1, 255) - data
-- unused: sum(reserved) - sum(used)
where indid in (0, 1, 255)
left join
(
select
id
, sum(reserved) as sum_reserved
, sum(used) as sum_used
from
sysindexes
where
indid in (0, 1, 255)
group by
id
) j_ru
on j_ru.id = so.id
-- data: sum(dpages) where indid <
2
-- + sum(used) where indid = 255
(text)
left join
(
select
j_dpages.id
, coalesce(j_dpages._sum,0) +
coalesce(j_used._sum,0) [data]
from
(
select
id
, sum(dpages) [_sum]
from
sysindexes
where
indid <
2
group by
id
) j_dpages
left join
(
select
id
, sum(used) [_sum]
from
sysindexes
where
indid =
255
group by
id
) j_used
on j_used.id = j_dpages.id
) d
on d.id = so.id
where
objectproperty(so.id, N'IsUserTable') = 1
order by
[DATA
(KB)] DESC,
[ROWCOUNT] ASC