SQL Table Space

I was doing capacity planning the other day and I needed to know which tables take how much space, how many rows they have etc'.

There's a system stored procedure for that, sp_spaceused, but it only works on the whole database or a specific table and i tried pushing its results into a table and sorting by it, but it converts the numbers to a string so the order didn't exactly worked.

so I looked in the sp's innards and wrote the following:

select stats.name,
      row_count,
       stats.reserved_page_count * 8 as reservedKB,
       stats.pages * 8 as dataKB,
       (CASE WHEN stats.used_page_count > stats.pages THEN (stats.used_page_count - stats.pages) ELSE 0 END) * 8 as index_sizeKB,
       (CASE WHEN stats.reserved_page_count > stats.used_page_count THEN (stats.reserved_page_count - stats.used_page_count) ELSE 0 END) * 8 as unusedKB
from
(
    select name, 
           
            sum(reserved_page_count) as reserved_page_count, 
            sum(used_page_count) as 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
                ) as pages
                
    FROM sys.dm_db_partition_stats
    join sys.objects on sys.objects.object_id = sys.dm_db_partition_stats.object_id
        WHERE sys.objects.type = 'U'
    group by name,sys.dm_db_partition_stats.object_id
) as stats
join
(
    select name, max(row_count) as row_count
    FROM sys.dm_db_partition_stats
        join sys.objects on sys.objects.object_id = sys.dm_db_partition_stats.object_id
            WHERE sys.objects.type = 'U'
    group by name
) as rowcountstats
    on rowcountstats.name = stats.name

order by row_count desc

Tags: dm_db_partition_stats, rows, sp_spaceused, space, sql