Find table sizes for all database tables

Question

I've been looking for several days to find a way of finding the table sizes for every database within the current SQL instance. Currently im able to get the tables sizes for the current database without issue using sp_spaceused, is there a way of using sp_MSforeachdb to get the size information?

I can list all the tables but not sure how you would add the table sizes.

Declare @temp_table table
(
    DB_NAME varchar(max),
    TABLE_OWNER varchar(max),
    TABLE_NAME varchar(max),
    TABLE_TYPE varchar(max),
    REMARKS varchar(max)
)

INSERT INTO @temp_table (DB_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE,REMARKS)
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_tables'

SELECT DB_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE
FROM @temp_table 
WHERE DB_NAME not in ('master','model','msdb','tempdb') and [TABLE_TYPE] = 'TABLE'
order by 1,2,3

Any advice would be welcomed.

Thanks in advance..

Answer 1

If table size is your issue than you can calculate as below:

Declare @temp_table table
(
    DBNAME varchar(max),
    TABLE_Name varchar(max),
      table_schema varchar(max),
      row_count int,
    TABLE_Size_in_kb numeric(10,2) 
)

INSERT INTO @temp_table 
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT 
    ''?'',
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB 
    from
      sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE ''dt%'' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows'

SELECT *
FROM @temp_table 
WHERE DBNAME not in ('master','model','msdb','tempdb') 
order by 1,2,3