SQL query that shows detailed sizes of tables in SQL database..

SQL query that shows detailed sizes of tables in SQL database..

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