Lists subscription information associated with a particular publication, article, Subscriber, or set of subscriptions. This stored procedure is executed at a Publisher on the publication database.
sp_helpsubscription [ [ @publication = ] 'publication' ]
[ , [ @article = ] 'article' ]
[ , [ @subscriber = ] 'subscriber' ]
[ , [ @destination_db = ] 'destination_db' ]
[ , [ @found =] found OUTPUT ]
[@publication = ] 'publication'
Is the name of the associated publication. publication is sysname, with a default of %, which returns all subscription information for this server.
[@article = ] 'article'
Is the name of the article. article is sysname, with a default of %, which returns all subscription information for the selected publications and Subscribers. If all, only one entry is returned for the full subscription on a publication.
[@subscriber = ] 'subscriber'
Is the name of the Subscriber on which to obtain subscription information. subscriber is sysname, with a default of %, which returns all subscription information for the selected publications and articles.
[@destination_db = ] 'destination_db'
Is the name of the destination database. destination_db is sysname, with a default of %.
[@found = ] 'found' OUTPUT
Is a flag to indicate returning rows. found is int and an OUTPUT parameter, with a default of 23456. 1 indicates the publication is found. 0 indicates the publication is not found.
|Column name||Data type||Description|
|subscriber||sysname||Name of the Subscriber.|
|publication||sysname||Name of the publication.|
|article||sysname||Name of the article.|
|destination database||sysname||Name of the destination database in which replicated data is placed.|
|subscription status||tinyint||Subscription status:
0 = Inactive
|synchronization type||tinyint||Subscription synchronization type:
1 = Automatic
|subscription type||int||Type of subscription:
0 = Push
|Full subscription||bit||Whether subscription is to all articles in the publication:
0 = No
|subscription name||nvarchar(255)||Name of the subscription.|
|update mode||int||0 = Read-only |
1 = Immediate-updating subscription
|distribution job id||binary(16)||Job ID of the Distribution Agent.|
|loopback_detection||bit||0 = No |
1 = Yes
|offload_enabled||bit||Specifies whether offload execution of a replication agent has been set to run at the Subscriber. If 0, agent is run at the Publisher. If 1, agent is run at the Subscriber.|
|offload_server||sysname||Name of the server enabled for remote agent activation. If NULL, then the current offload_server listed in MSDistribution_agents table is used.|
|Dts_package_name||sysname||Specifies the name of the DTS package.|
|Dts_package_location||int||Location of the DTS package, if one is assigned to the subscription. If there is a package, a value of 0 specifies the package location at the distributor. A value of 1 specifies the subscriber.|
sp_helpsubscription is used in snapshot, transactional, and merge replication.
Execute permissions default to the public role. However, sysadmin fixed server role or db_owner fixed database role can see all subscriptions, while the other users get a result set listing only their own subscriptions.