Table names as variables (Replication row count check.)

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 1

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: 2482 More actions · Answer 2

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 · Answer 3

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