How to know the table size in DB

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