syspublications (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

syspublications (Transact-SQL)

 

Applies To: SQL Server

Contains one row for each publication defined in the database. This table is stored in the publication database.

Column nameData typeDescription
descriptionnvarchar(255)The descriptive entry for the publication.
namesysnameThe unique name associated with the publication.
pubidintThe identity column providing a unique ID for the publication.
repl_freqtinyintThe replication frequency:

 0 = Transaction based.

 1 = Scheduled table refresh.
statustinyintThe status:

 0 = Inactive.

 1 = Active.
sync_methodtinyintThe synchronization method:

 0 = Native-mode bulk-copy program utility (BCP).

 1 = Character-mode BCP.

 3 = Concurrent, which means that native-mode BCP is used but tables are not locked during the snapshot.

 4 = Concurrent_c, which means that character-mode BCP is used but tables are not locked during the snapshot.
snapshot_jobidbinary(16)The scheduled task ID.
independent_agentbitSpecifies whether there is a stand-alone Distribution Agent for this publication.

 0 = The publication uses a shared Distribution Agent, and each Publisher database/Subscriber database pair has a single, shared Agent.

 1 = There is a stand-alone Distribution Agent for this publication.
immediate_syncbitIndicates whether the synchronization files are created or recreated each time the Snapshot Agent runs, where 1 means that they are created every time the agent runs.
enabled_for_internetbitIndicates whether the synchronization files for the publication are exposed to the Internet through file transfer protocol (FTP) and other services, where 1 means that they can be accessed from the Internet.
allow_pushbitIndicates whether push subscriptions are allowed on the publication, where 1 means that they are allowed.
allow_pullbitIndicates whether pull subscriptions are allowed on the publication, where 1 means that they are allowed.
allow_anonymousbitIndicates whether anonymous subscriptions are allowed on the publication, where 1 means that they are allowed.
immediate_sync_readybitIndicates whether the snapshot has been generated by the Snapshot Agent and is ready for use by new subscriptions. It is only meaningful for immediate updating publications. 1 indicates that the snapshot is ready.
allow_sync_tranbitSpecifies whether immediate-updating subscriptions are allowed on the publication. 1 means that immediate-updating subscriptions are allowed.
autogen_sync_procsbitSpecifies whether the synchronizing stored procedure for immediate-updating subscriptions is generated at the Publisher. 1 means that it is generated at the Publisher.
retentionintThe amount of change, in hours, to save for the given publication.
allowed_queued_tranbitSpecifies whether queuing of changes at the Subscriber until they can be applied at the Publisher has been enabled. If 1, changes at the Subscriber are queued.
snapshot_in_defaultfolderbitSpecifies whether snapshot files are stored in the default folder.

 0 = Snapshot files have been stored in the alternate location specified by alternate_snapshot_folder.

 1 = Snapshot files can be found in the default folder.
alt_snapshot_foldernvarchar(255)Specifies the location of the alternate folder for the snapshot.
pre_snapshot_scriptnvarchar(255)Specifies a pointer to a .sql file location. The Distribution Agent runs the pre-snapshot script before running any of the replicated object scripts when applying a snapshot at a Subscriber.
post_snapshot_scriptnvarchar(255)Specifies a pointer to a .sql file location. The Distribution Agent runs the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization.
compress_snapshotbitSpecifies that the snapshot that is written to the alt_snapshot_folder location is to be compressed into the Microsoft CAB format.1 means that the snapshot will be compressed.
ftp_addresssysnameThe network address of the FTP service for the Distributor. Specifies where publication snapshot files are located for the Distribution Agent to pick up.
ftp_portintThe port number of the FTP service for the Distributor. Specifies where the publication snapshot files are located for the Distribution Agent to pick up
ftp_subdirectorynvarchar(255)Specifies where the snapshot files will be available for the Distribution Agent to pick up if the publication supports propagating snapshots using FTP.
ftp_loginsysnameThe username used to connect to the FTP service.
ftp_passwordnvarchar(524)The user password used to connect to the FTP service.
allow_dtsbitSpecifies whether the publication allows data transformations. 1 specifies that DTS transformations are allowed.
allow_subscription_copybitSpecifies whether the ability to copy the subscription databases that subscribe to this publication has been enabled. 1 means that copying is allowed.
centralized_conflictsbitSpecifies whether conflict records are stored on the Publisher:

 0 = Conflict records are stored at both the publisher and at the subscriber that caused the conflict.

 1 = Conflict records are stored at the Publisher.
conflict_retentionintSpecifies the conflict retention period, in days.
conflict_policyintSpecifies the conflict resolution policy followed when the queued updating subscriber option is used. Can be one of these values:

 1 = Publisher wins the conflict.

 2 = Subscriber wins the conflict.

 3 = Subscription is reinitialized.
queue_typeintSpecifies which type of queue is used. Can be one of these values:

 1 = msmq, which uses Microsoft Message Queuing to store transactions.

 2 = sql, which uses SQL Server to store transactions.

Note: Using Microsoft Message Queuing has been deprecated and is no longer available.
ad_guidnamesysnameSpecifies whether the publication is published in the Microsoft Active Directory. A valid globally unique identifier (GUID) specifies that the publication is published in the Active Directory, and the GUID is the corresponding Active Directory publication object objectGUID. If NULL, the publication is not published in Active Directory.
backward_comp_levelintDatabase compatibility level, which can be one of the following values:

 90 = SQL Server 2005.

 100 = SQL Server 2008.

 110 = SQL Server 2012.

 120 = SQL Server 2014.
allow_initialize_from_backupbitIndicates whether Subscribers can initialize a subscription to this publication from a backup rather than an initial snapshot. 1 means that subscriptions can be initialized from a backup, and 0 means that they cannot. For more information, see Initialize a Transactional Subscription Without a Snapshot.
min_autonosync_lsnbinaryIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
replicate_ddlintIndicates whether schema replication is supported for the publication. 1 indicates that data definition language (DDL) statements executed at the publisher are replicated, and 0 indicates that DDL statements are not replicated. For more information, see Make Schema Changes on Publication Databases.
optionsintA bitmap that specifies additional publishing options, where the bitwise option values are as follows:

 0x1 - Enabled for peer-to-peer replication.

 0x2 - Publish only local changes for peer-to-peer replication.

 0x4 - Enabled for non-SQL Server subscribers.

 0x8 - Enabled for peer-to-peer conflict detection.
originator_idsmallintIdentifies each node in a peer-to-peer replication topology for the purposes of conflict detection. For more information, see Conflict Detection in Peer-to-Peer Replication.

Replication Tables (Transact-SQL)
Replication Views (Transact-SQL)
sp_addpublication (Transact-SQL)
sp_changepublication (Transact-SQL)
sp_helppublication (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft