TableSizesAcrossEntireServer.sql

use master;
go

create table ##tablesizes
(
    db varchar(100),
    name varchar(100),
    sizeMB float
);

exec sp_ineachdb @command =
'INSERT INTO ##tablesizes
 SELECT db=''?'', sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as sizeMB
 FROM sys.dm_db_partition_stats, sys.objects
 WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
 GROUP BY sys.objects.name
 ORDER BY sizeMB DESC ;
',
@help=1

select top 100 * from ##tablesizes order by sizeMB desc;

select top 50 name, sum(sizeMB) as TotalSizeMB from ##tablesizes group by name order by TotalSizeMB desc;

drop table ##tablesizes;