Thanks, but this script only works if you have a local distribution database (and that it is named [distribution]).  The publication/article data is available in each publisher DB in [dbo].[syspublications] and [dbo].[sysarticles] tables that you could include in your MSforeachdb command.


-- create table with only the names of databases that are published
SELECT name as [DatabaseName]
INTO #tmpPubDatabases
FROM sys.databases
WHERE database_id > 4
AND is_published = 1;

-- create table to hold the table info (name, schema,row count, space used)
CREATE TABLE #tmpTableSizes(
DBName VARCHAR(256),
PublicationName VARCHAR(256),
ArticleName VARCHAR(256),
SchemaName VARCHAR(256),
TableName VARCHAR(256),
RowCounts INT,
TotalSpaceMB DECIMAL(18,2)
);

DECLARE @command VARCHAR(MAX);

-- run this in all the databases that have publications
SET @command = '
USE [?]

IF DB_NAME() IN (SELECT DatabaseName FROM #tmpPubDatabases)

INSERT #tmpTableSizes
    SELECT
  DB_NAME()          AS [DBName],
  [sp].[name]         AS [PublicationName],
  [sa].[name]         AS [ArticleName],
  .[name]          AS [SchemaName],
  [t].[name]          AS [TableName],
  [p].[rows]          AS [RowCounts],
  (SUM([a].[total_pages]) * 8) / 1024.0 AS [TotalSpaceMB]
FROM
  [dbo].[syspublications] [sp]
INNER JOIN [dbo].[sysarticles] [sa]
  ON [sa].[pubid] = [sp].[pubid]
INNER JOIN [sys].[tables] [t]
  ON [t].[object_id] = [sa].[objid]
INNER JOIN [sys].[indexes]
  ON [t].[object_id] = .[object_id]
INNER JOIN [sys].[partitions] [p]
  ON .[object_id] = [p].[object_id]
   AND .[index_id] = [p].[index_id]
INNER JOIN [sys].[allocation_units] [a]
  ON [p].[partition_id] = [a].[container_id]
LEFT OUTER JOIN [sys].[schemas]
  ON [t].[schema_id] = .[schema_id]
WHERE
  [t].[name] NOT LIKE ''dt%''
  AND [t].[is_ms_shipped] = 0
  AND .[object_id] > 255
GROUP BY
  [sp].[name],
  [sa].[name],
  .[name],
  [t].[name],
  [p].[rows]
';

-- run for all affected databases
EXEC sp_MSforeachdb @command

SELECT
  *
FROM
  [#tmpTableSizes] AS [tts]
ORDER BY
  [tts].[DBName],
  [tts].[PublicationName],
  [tts].[TableName];

-- clean up
DROP TABLE #tmpTableSizes;
DROP TABLE #tmpPubDatabases;