Technical Article

Find all published tables with row counts and table size

,

Sometimes you need to list all the tables that are being published on a server.  Once you have the list it is really nice to have the table sizes too (row count and total MBs for table).  This script will do the following.

  1. It looks at all the databases, finds the ones that are published
  2. Then if grabs the row counts and table sizes for tables in those databases
  3. Then compares that to the articles in the publications and gives you a nice easy to read report

Please Note: This assumes no filters on the publication.  If you have a very large table and are only replicating 5 of 60 columns, if will not see that and will give you the numbers for the entire table.

-- 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),
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)
BEGIN
INSERT #tmpTableSizes
SELECT 
db_Name(),
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
(SUM(a.total_pages) * 8)/1024.0 AS TotalSpaceMB
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
--AND t.Name IN ()
GROUP BY 
t.Name, s.Name, p.Rows
ORDER BY 
t.Name
END';

-- run for all affected databases
EXEC sp_MSforeachdb @command


-- this will match the publications to the tables and give the you row count and sizes
-- run this in the distribution database

SELECT
     P.[publication]   AS [PublicationName]
    ,A.[publisher_db]  AS [DatabaseName]
    ,A.[article]       AS [ArticleName]
    ,A.[source_owner]  AS [Schema]
    ,A.[source_object] AS [Table]
,T.RowCounts
,T.TotalSpaceMB
FROM
    [distribution].[dbo].[MSarticles] AS A
    INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id])
LEFT JOIN #tmpTableSizes T ON A.[publisher_db] = T.DBName AND A.[source_owner] = T.SchemaName AND A.[source_object] = T.TableName

ORDER BY
    P.[publication], A.[article];


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

Rate

3.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3.67 (3)

You rated this post out of 5. Change rating