Update on Getting Table Row Count(For Replicated Data)

After seeing my last post, I got a few requests:

• Can't we resolve the schema issue with sp_SpaceUsed?
• Can we modify the query to handle multiple susbcriptions bit more efficiently?

Let me explain the second issue first. the query in the post calculates the row count each time for each subscription.  It could be written efficiently for multiple subscriptions.


The first issue could be resolved using temp table.  Even though we can't add or remove to the output, we can execute an update statement to do that.   Below is the query for that.


DECLARE @SQL varchar(max)
SET @SQL = ''
CREATE TABLE #Temp (
TempID int identity,
ArtID int NULL,
DBName varchar(100) null,
SchemaName varchar(100)null,
TableName varchar(100),
rows bigint,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100)
);


SELECT @SQL = @SQL+'
INSERT INTO #Temp(TableName, rows, reserved, data, index_size, unused) EXEC sp_SpaceUsed '''+ OBJECT_SCHEMA_NAME(a.objid)+'.'+ OBJECT_NAME(a.objid)+''';
UPDATE #Temp SET ArtID = '+ CONVERT(varchar, a.artid) +', DBName ='''+ DB_NAME()+''', SchemaName = '''+ OBJECT_SCHEMA_NAME(a.objid)+''' WHERE DBName IS NULL;'
FROM dbo.sysarticles a

SELECT @SQL = @SQL+'
INSERT INTO #Temp(TableName, rows, reserved, data, index_size, unused) EXEC '+ s.dest_db +'..sp_SpaceUsed '''+ a.dest_owner+'.'+ a.dest_table+''';
UPDATE #Temp SET ArtID = '+ CONVERT(varchar, a.artid) +', DBName ='''+ s.dest_db +''', SchemaName = '''+ a.dest_owner+''' WHERE DBName IS NULL;'
FROM dbo.sysarticles a
INNER JOIN dbo.syssubscriptions s
ON a.artid = s.artid
EXEC(@SQL)
SELECT * FROM #Temp ORDER BY ArtID, TempID
DROP TABLE #Temp

 
For the second question I have a query which can do the magic without a temp table, (It uses derived table though) IF you have replciation setup in your environment, you may be able to get the values and check the results.

DECLARE @SQL varchar(max)
SET @SQL = ''
SELECT @SQL = @SQL+Query FROM (
SELECT a.artid,1 as rowid, '
SELECT '''+DB_NAME() + ''' AS DBName,
'''+ OBJECT_SCHEMA_NAME(a.objid)+''' AS SchemaName,
'''+ OBJECT_NAME(a.objid) +''' AS TableName,
COUNT(*) as row_count
FROM '+DB_NAME() +'.'+ OBJECT_SCHEMA_NAME(a.objid)+'.'+ OBJECT_NAME(a.objid) AS query
FROM dbo.sysarticles a
UNION ALL
SELECT a.artid,2 as rowid,
' UNION ALL
SELECT '''+s.dest_db + ''' AS DBName,
'''+ a.dest_owner +''' AS SchemaName,
'''+ a.dest_table +''' AS TableName,
COUNT(*) as row_count
FROM '+ s.dest_db +'.'+ a.dest_owner +'.'+ a.dest_table
FROM dbo.sysarticles a
INNER JOIN dbo.syssubscriptions s
ON a.artid = s.artid
)a
ORDER BY a.artid, rowid
EXEC(@SQL)



Hope this helps!