Quick dump of All table Sizes of a database

Option #1 - Clear documented and robust

create table tempdb.dbo.AllTableSize058(name varchar(150), rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
go
sp_msforeachtable 'Insert into tempdb.dbo.AllTableSize058 Exec sp_spaceused "?"'

Update tempdb.dbo.AllTableSize058 set reserved = replace(reserved, 'KB', ''), data = replace(data, 'KB', ''), index_size = replace(index_size, 'KB', ''), unused = replace(unused, 'KB', '')

select * from tempdb.dbo.AllTableSize058 order by cast(reserved as int) desc

--Drop table tempdb.dbo.AllTableSize058



Option #2

Undocumented way, but efficient and clean. Does not look for internal tables (service broker queues etc)


select Name, rows, reserved, used, unused, data, used - data index_size from
(
select a.object_id, a.name,
Sum(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END) rows,
Sum(reserved_page_count * 8) reserved,
Sum(used_page_count * 8) used,
Sum((reserved_page_count - used_page_count) * 8) unUsed,
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 * 8) data
from sys.tables a inner join sys.dm_db_partition_stats b
on a.object_id = b.object_id
Group by a.object_id, a.name
) a
order by reserved desc