none
Listing all Subscriptions on Subscriber RRS feed

  • Question

  • Hi All,

    Running SQL Server 2008 R2. 

    I need to return a list of all subscriptions configured on a given subscriber, across all databases.  All the research I've done to this point centers around returning the total number of subscriptions to a publication, but the query I'm after would be run on the subscriber, not the publisher.

    Any ideas are greatly appreciated!

    Best Regards

    Brad

    Thursday, June 22, 2017 6:28 PM

All replies

  • sp_msforeachdb 'select * from sysmergesubscriptions where subscriber_server=@@Servername'
    Thursday, June 22, 2017 6:34 PM
    Moderator
  • Hi Hilary,

    Thanks for the quick response!  Is there any way I could get the same results without having to invoke a stored procedure?

    Thursday, June 22, 2017 6:43 PM
  • select *  From sysmergesubscriptions 


    Thursday, June 22, 2017 6:45 PM
    Moderator
  • select *  From sysmergesubscriptions 


    I think this query work only for merge replication.

    As Im using mainly transactional and snapshot replication, I'm using this query on subscribers:

    select *  From msreplication_subscriptions

    You need to use sp_msforeachdb if u want to check all dbs as this table is located in each subscribed database.

    Also note that if subscription was removed somehow incorrectly u may find some old records in there (nut u can filter by time column).

    Friday, June 23, 2017 1:55 PM