is there a way to improve this query by not re calculating functions? cross apply? 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:



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 &lt;&gt; '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 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 &lt;&gt; '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