Table names as variables (Replication row count check.)

  • 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


    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


    select *

    from dbo.MSarticles

    where publisher_db='A_DB_Name'

    Can anyone help?

    Thanks in advance


    Graham Okely B App Sc
    Senior Database Administrator

  • 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.

  • 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:

  • 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