Find all published tables with row counts and table size

• Jim Youmans-439383

  SSCommitted

  Points: 1657

  More actions

  March 14, 2017 at 1:06 pm

  #322609

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

• tina.huaarroyo

  SSC Enthusiast

  Points: 104

  More actions

  March 16, 2017 at 10:03 am

  #1933537

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

• jeffgonnering

  Say Hey Kid

  Points: 662

  More actions

  March 16, 2017 at 10:55 am

  #1933559

  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;

• tina.huaarroyo

  SSC Enthusiast

  Points: 104

  More actions

  March 16, 2017 at 11:05 am

  #1933565

  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?

• Jim Youmans-439383

  SSCommitted

  Points: 1657

  More actions

  March 17, 2017 at 10:22 am

  #1933790

  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.