Below is script that I often use sql find table size that helps me to query tables and get summary of all tables in a database sorted by table size in MB. The script below was tested on SQL Server 2008 R2.
USE DatabaseName
IF object_id('tempdb..#TableSize') IS NOT NULL
BEGIN
DROP TABLE #TableSize
END
create table #TableSize (name varchar(150), rows int, reserved varchar(150)
,data varchar(150), index_size varchar(150), unused varchar(150))
insert into #TableSize
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''
select name, cast(replace(data, ' KB','') as int)/1024 as TableDataSizeMB
from #TableSize
order by cast(replace(data, ' KB','') as int) desc
drop table #TableSize
Take care
Emil
