March 14, 2017 at 1:06 pm
Comments posted to this topic are about the item Find all published tables with row counts and table size
March 16, 2017 at 10:03 am
I ran the script on SQL Sever2014 and got an error at lines
SET @command = '
.
.
.
END';
March 16, 2017 at 10:55 am
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;
March 16, 2017 at 11:05 am
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?
March 17, 2017 at 10:22 am
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