The below SQL script will return a list of tables in a database along with the table sizes in MB.
select object_name(id) [Table Name],
[Table Size] = convert (varchar, dpages * 8 / 1024) + 'MB'
from sysindexes where indid in (0,1)
order by dpages desc
You will get a report like the below;
