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

Is there a T-SQL query I can run against a database that will provide a list of all tables in that d开发者_高级运维atabase 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.

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

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;

Did you take a look at the Disk Usage Summary Report?

"To view the report, expand the Management folder, right-click Data Collection, point to Reports, point to Management Data Warehouse, and then click Disk Usage Summary"

The Disk Usage Collection Set report provides an overview of the disk space used for all databases in the instance of SQL Server, and growth trends for the data and log files for each of these databases.
- The summary table displays the start size (in megabytes) and the current size of all the databases installed on the server that the data collector is monitoring.
- Trend and average growth information is shown graphically and numerically for both data and log files.