Chris Miller Blog

RocketScientist's Miscellaneous Ramblings

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

        object_name(object_id) name, 
        SUM (reserved_page_count) reserved_page_count, 
        SUM (used_page_count) used_page_count, 
        SUM (
                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
            ) Pages,
        SUM (
                WHEN (index_id < 2) THEN row_count
                ELSE 0
            ) 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
        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