Find all published tables with row counts and table size

  • Comments posted to this topic are about the item Find all published tables with row counts and table size

  • I ran the script on SQL Sever2014 and got an error at lines
    SET @command = '
    .
    .
    .
    END';

  • 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;

  • Thanks for the tip on the database name.  What I don't understand is on the line Set @command = '.   This line has a single quote after the @command and the other quote is way after the END line.
    Do you mean the whole block from Use [database name] to End is meant to be included in the single quotes?

  • This script is meant to be run on the distributor in the distributor database as mentioned above.

    I am using the sp_MSforeachDB stored procedure which takes a query as a parameter and runs it against each DB on the server.  The SQL that is being run against each database is in the @command variable.  That is the reason for the SQL in single quotes.

    The ? will be replace by the database name as the sp_MSforeachDB cycles through each database on the server.  Then the rest of the SQL will be ran in that database.

    Sorry if that was not clear.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply