1

when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures.

this is the picture from the publisher:

enter image description here

in order to generate this picture I run the following query:

USE ORCASTG_CA18_Repl
go
SELECT 
         Publication=P.name 
        --,Publication_description=p.description
        ,p.immediate_sync
        ,p.allow_anonymous
        ,p.replicate_ddl
        ,the_schema=OBJECT_SCHEMA_NAME(a.objid)
        ,TableName = a.name
        ,DestinationServer=s.srvname
        ,DestinationDB=s.dest_db
        ,DestinationSchema= a.dest_owner
        ,DestinationTable = A.dest_table  
        ,Radhe='exec sp_count ' +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid)) + '.' + QUOTENAME(a.name) + ''''

FROM dbo.syspublications P 

INNER JOIN dbo.sysarticles A 
        ON P.pubid = A.pubid

INNER JOIN dbo.syssubscriptions s 
        ON a.artid = s.artid 

WHERE 1=1
  AND s.dest_db <> 'virtual'

and that produces the following script:

exec sp_count '[dbo].[repl_application_placement]'
exec sp_count '[dbo].[repl_ApplicationCalendarRequest]'
exec sp_count '[dbo].[repl_ApplicationChecklist]'
exec sp_count '[dbo].[repl_ApplicationFlightDetail]'
exec sp_count '[dbo].[repl_ApplicationFlightLegDetail]'
exec sp_count '[dbo].[repl_ApplicationFlightReference]'
exec sp_count '[dbo].[repl_ApplicationProfile]'
exec sp_count '[dbo].[repl_ApplicationRequestFlightOption]'
exec sp_count '[dbo].[repl_ApplicationSkill]'
exec sp_count '[dbo].[repl_ApplicationVisaDetail]'
exec sp_count '[dbo].[repl_camp_profile]'

My question is:

Is there a way I can calculate the following line without re-running those functions:

,Radhe='exec sp_count ' +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid)) + '.' + QUOTENAME(a.name) + ''''

I thought about cross apply!

  • I am trying to avoid calculating OBJECT_SCHEMA_NAME(a.objid) twice for each row. Let alone if there is any benefit in doing it, is it something possible? – Marcello Miorelli Sep 27 '18 at 9:29
0

I beleive you want to store the results of the multiple procs in a single run. If that's the case then

I am not sure about what are the columns returned by the procedure sp_count I am assuming that it returns tablename,total_count

So your code will be like this

USE ORCASTG_CA18_Repl
go
DECLARE @sql VARCHAR(MAX);

CREATE TABLE #table_count (table_name varchar(128), table_count INT)

  SELECT  @sql=COALESCE(@sql+ '', '') 
           + 'INSERT INTO #table exec sp_count ' 
           +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid)) 
           + '.' + QUOTENAME(a.name) +';'+'''')

FROM dbo.syspublications P 

INNER JOIN dbo.sysarticles A 
        ON P.pubid = A.pubid

INNER JOIN dbo.syssubscriptions s 
        ON a.artid = s.artid 

WHERE 1=1
  AND s.dest_db <> 'virtual'

  EXEC (@sql)

  SELECT * FROM #table_count tc

Also note, I have not tested the code. If you find issues when running please feel free to edit it.

Edit 1: You can also use the below methods instead of COALESCE()

STRING_AGG() (2017 onwards)

XML string concatenation

GROUP_CONCAT CLR method (Github)

All these is mentioned in the Aaron bertrand's blog here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.