July 14, 2008 at 8:50 pm
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
July 15, 2008 at 12:01 pm
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.
July 15, 2008 at 3:47 pm
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:
July 15, 2008 at 7:55 pm
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy