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.