this is much better:

IF EXISTS (SELECT OBJECT_ID('tempdb.dbo.#tmp'))

DROP TABLE #tmp

CREATE TABLE [dbo].[#tmp] (

[DBName] NVARCHAR(128) NULL,

[TableName] SYSNAME NOT NULL,

[SchemaName] SYSNAME NULL,

[RowCounts] BIGINT NOT NULL,

[TotalSpaceKB] BIGINT NULL,

[UsedSpaceKB] BIGINT NULL,

[UnusedSpaceKB] BIGINT NULL)

EXEC sp_MsForEachDB '

USE [?];

INSERT INTO #tmp

SELECT

db_name() AS DBName,

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

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

ORDER BY

t.Name'

--top 10 each db

SELECT * FROM (

SELECT

ROW_NUMBER() OVER (PARTITION BY DBName ORDER BY TotalSpaceKB DESC) As RW,*

FROM #tmp ) MyAlias

WHERE RW <=10