How to list all table sizes in a database?

Execute the following Microsoft SQL Server T-SQL script to list the database and each table size within the AdventureWorks2008 database. If you need to store size information for long-term tracking of database growth, permanent table can be used instead of @tablevariable. 

use AdventureWorks2008;

go

-- Space used by AdventureWorks2008 database

exec sp_spaceused

go

/* Results

database_name     database_size     unallocated space

AdventureWorks2008      182.06 MB   0.00 MB

reserved    data  index_size  unused

184608 KB   96112 KB    82064 KB    6432 KB

*/

-- Space used for all tables

declare @TableSpace table (TableName sysname, RowsK varchar(32),

      ReservedMB varchar(32), DataMB varchar(32),

      IndexSizeMB varchar(32), UnusedMB varchar(32))

     

insert @TableSpace

exec sp_MSforeachtable @command1="exec sp_spaceused '?';"

update @TableSpace set RowsK = CONVERT(varchar,

      1+convert(int, RowsK)/1024)

update @TableSpace set ReservedMB = CONVERT(varchar,

      1+convert(int,LEFT(ReservedMB, charindex(' K', ReservedMB,-1)))/1024)

update @TableSpace set DataMB = CONVERT(varchar,

      1+convert(int,LEFT(DataMB, charindex(' K', DataMB,-1)))/1024)

update @TableSpace set IndexSizeMB = CONVERT(varchar,

      convert(int,LEFT(IndexSizeMB, charindex(' K', IndexSizeMB,-1)))/1024)

update @TableSpace set UnusedMB = CONVERT(varchar,

      convert(int,LEFT(UnusedMB, charindex(' K', UnusedMB,-1)))/1024)

     

select * from @TableSpace order by convert(int,DataMB) desc

go

Partial results:

TableName	RowsK	ReservedMB	DataMB	IndexSizeMB	UnusedMB
Person	20	82	30	51	0
SalesOrderDetail	119	16	10	5	0
DatabaseLog	2	7	7	0	0
TransactionHistory	111	10	7	3	0

Related articles:

Display the size of all tables in Sql Server 2005