After my recent post, I had a couple of conversations and an email exchange with a colleague of mine and I was reminded of the fact that I went originally out to search for my 'original' table space used script. I couldn't find it online (even though I found it on my hdd) and that prompted me to put together the newer method. Which is considerably shorter. But here is my original script which essentially I wrote by looking at what was in sp_spaceused and just formatting it more to my liking.
-- 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