Tables, and the space they use
So I hacked around in sp_spaceused and got this sorted out. Works in SQL 2005, haven't tested north or south of that.
Share and enjoy.
drop table #mainpartitionstats
drop table #LOBPartitionStats
drop table #totals
SELECT
object_name(object_id) name,
SUM (reserved_page_count) reserved_page_count,
SUM (used_page_count) used_page_count,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
) Pages,
SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
) RC
into #MainPartitionStats
FROM sys.dm_db_partition_stats
group by object_name(object_id)
SELECT object_name(p.object_id) name,
sum(reserved_page_count) Reserved_page_count,
sum(used_page_count) Used_page_Count
into #LOBPartitionStats
FROM sys.dm_db_partition_stats p
inner join sys.internal_tables it
on p.object_id = it.object_id
WHERE it.internal_type IN (202,204)
group by object_name(p.object_id)
select name, sum(reserved_page_count) reserved_page_Count, sum(used_page_count) used_page_Count,
sum(Pages) pages, sum(rc) rc
into #totals
from (
select name, reserved_page_count, used_page_count, pages, rc
from #mainpartitionstats
union
select name, reserved_page_count, used_page_count, 0 pages, 0 rc
from #LOBpartitionstats
) c
group by name
SELECT name, rc as [rows],
reserved_page_count * 8 Reserved_Space_KB,
pages * 8 Data_Space_KB,
case when used_page_count > pages then (used_page_count - pages) * 8 else 0 end as index_space_KB,
case when reserved_page_count > used_page_count then (reserved_page_count - used_page_count) * 8 end as unused_space_kb
from #totals
order by 3 desc