none
script to list published dbs, objects and destination server and db

    Question

  • I have replication on SQL 2000 and on 2005. I would like to run a script to list out the published server, database, objects and its destination server and database. Also like to know who the distributor is.

     

    Thanks

    Wednesday, September 28, 2011 2:10 PM

All replies

  • Btw, I need a script for both 2000 and 2005 if they are not compatible
    Wednesday, September 28, 2011 2:10 PM
    • Proposed as answer by Peja Tao Thursday, September 29, 2011 6:17 AM
    Wednesday, September 28, 2011 4:55 PM
    Moderator
  • Let me check and get back to you.. Thanks for replying. I thinking I waited on the other thread for a while ( before you replied to it ) and thought I'd post again.
    Thursday, September 29, 2011 12:03 AM
  • Got this error when ran against the published database. We are using SQL 2005

     

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sys.tables'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'dbo.sysarticles'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'dbo.syssubscriptions'.

     

    Our distributor is on another server

    Thursday, September 29, 2011 9:09 PM
  • Did the USE statement fail?

    According to BOL, SQL 2005 should have sys.tables, sysarticles, and syssubscriptions.

    Try running this against your publication db:

    --Which tables in the database are published for Transactional Replication?
    SELECT 
    HOST_NAME() AS Publisher,
    DB_NAME() AS PublisherDB,
    a.name as PublishedTable,
    a.is_published as is_tran_published,
    c.srvname AS Subscriber,
    c.dest_db AS SubscriberDB
    FROM sys.tables a
    INNER JOIN dbo.sysarticles b ON a.object_id = b.objid
    INNER JOIN dbo.syssubscriptions c ON b.artid = c.artid
    WHERE a.is_published = 1
    


    Hope this helps.
    www.sqlrepl.com

    Tuesday, October 04, 2011 3:54 AM
    Moderator