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
Lowell