Table names as variables (Replication row count check.)

• GrahamO-735217

  SSC Enthusiast

  Points: 173

  More actions

  July 14, 2008 at 8:50 pm

  #77040

  I am working on a stored procedure and would like to compare row counts on published tables between two servers.

  select count(*) form Server1.database.dbo.@table

  and

  select count(*) form Server2.database.dbo.@table

  I want to loop through the published tables but how do you do that?

  This gives me the published table list:

  Use Distribution

  GO

  select *

  from dbo.MSarticles

  where publisher_db='A_DB_Name'

  Can anyone help?

  Thanks in advance

  Graham

  Graham Okely B App Sc
  Senior Database Administrator

• Wildcat

  SSCarpal Tunnel

  Points: 4774

  More actions

  July 15, 2008 at 12:01 pm

  #843304

  1. Create a table like:

  TableName RowCount1 RowCount2

  2. Insert the article names from Distribution into the above table.

  3. Write a loop to get every RowCount from each table in both evironments.

  4. Check result of that table.

  5. DONE.

• Fred Williams-134192

  SSCrazy

  Points: 2532

  More actions

  July 15, 2008 at 3:47 pm

  #843444

  Transact-SQL does not allow constructions like those.

  The closest you can come would be to build the entire query as a string, then execute the string using exec() or sp_execute_sql().

  This technique is called dynamic sql, and has pros and cons, including security and performance considerations, and practical ones such as executing in a new context.

  While the technique is useful for administrative purposes like yours, in the more general sense, using dynamic sql for substituting object names, etc. into queries is often considered *not* a good practice, especially for application code.

  For a comprehensive article (why and why not to, besides the how to) see:

  http://www.sommarskog.se/dynamic_sql.html

• Lester Policarpio

  SSCertifiable

  Points: 7109

  More actions

  July 15, 2008 at 7:55 pm

  #843474

  If you are using linked server you can use this syntax :

  exec [linked-server].db_name.dbo_owner.sp_msforeachtable 'select ''?'' as ''Table_Name'',count(*) as ''Table_Count'' from [server_name].db_name.?'

  If you are running from the local server you can use this syntax :

  exec db_name..sp_msforeachtable 'select ''?'' as ''Table_Name'',count(*) ''Table_Count'' from [server_name].db_name.?'

  from there you can create a table and insert the values from the stored procedure sp_msforeachtable to the table

  "-=Still Learning=-"

  Lester Policarpio