How to retrieve a result set of all tables in the DB with their disk space usage

Question

Is there a T-SQL query I can run against a database that will provide a list of all tables in that database as well as their current disk space usage? I know I can look at it in SSMS with the "properties" dialog, and I know how to use the sp_spaceused sproc to see it one table at a time but I'd like to evaluate the disk space usage of all of my tables, ordered by highest disk space usage to lowest. We need to cut our DB size down significantly so I'd like to see which tables are the worst offenders.

Answer 1

create table #Temp (
    name nvarchar(128),
    [rows] char(11),
    reserved varchar(18),
    data varchar(18),
    index_size varchar(18),
    unused varchar(18)
)

insert into #Temp
    exec sp_msforeachtable 'sp_spaceused ''?'''

select * from #Temp order by cast(replace(reserved,' kb','') as int) desc

Answer 2

sys.allocation_units, look at total_pages. Each rowset (partition of an index) has 3 allocation units (DATA, SLOB and LOB), see Table and Index Organization. Join with sys.partitions to get the object_id and the index_id. Index_id 0is the heap of an unordered table, index id 1 is the clustered index. Every table (index) has at least one partition, if is not partitioned:

select object_name(p.object_id) as [name],
    object_schema_name(p.object_id) as [schema],
    i.name as [index],
    i.type_desc,
    au.type_desc,
    p.partition_number,
    p.rows,
    au.total_pages * 8 as [space (kb)]
from sys.allocation_units au
join sys.partitions p on au.container_id = p.partition_id
join sys.indexes i on p.object_id = i. object_id
    and i.index_id = p.index_id
order by [space (kb)] desc;