How to: Delete a Publication (Replication Transact-SQL Programming)

Publications can be deleted programmatically using replication stored procedures. The stored procedures that you use depend on the type of publication being deleted.

附註：
Deleting a publication does not remove published objects from the publication database or the corresponding objects from the subscription database. Use the DROP <object> command to manually remove these objects if necessary.

To delete a snapshot or transactional publication

1. Do one of the following:

   • To delete a single publication, execute sp_droppublication at the Publisher on the publication database.
   • To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. Specify a value of tran for @type. (Optional) If the Distributor cannot be accessed or if the status of the database is suspect or offline, specify a value of 1 for @force. (Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

     附註：
     Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

2. (Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using snapshot or transactional replication.

3. (Optional) At the Subscriber on the subscription database, execute sp_subscription_cleanup to remove any remaining replication metadata in the subscription database.

To delete a merge publication

1. Do one of the following:

   • To delete a single publication, execute sp_dropmergepublication (Transact-SQL) at the Publisher on the publication database.
   • To delete all publications in and remove all replication objects from a published database, execute sp_removedbreplication at the Publisher. Specify a value of merge for @type. (Optional) If the Distributor cannot be accessed or if the status of the database is suspect or offline, specify a value of 1 for @force. (Optional) Specify the name of the database for @dbname if sp_removedbreplication is not executed on the publication database.

     附註：
     Specifying a value of 1 for @force may leave replication-related publishing objects in the database.

2. (Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using merge replication.

3. (Optional) At the Subscriber on the subscription database, execute sp_mergesubscription_cleanup (Transact-SQL) to remove any remaining replication metadata in the subscription database.

範例

This example shows how to remove a transactional publication and disable transactional publishing for a database. This example assumes that all subscriptions were previously removed. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming) or How to: Delete a Push Subscription (Replication Transact-SQL Programming).

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks'; 
SET @publication = N'AdvWorksProductTran'; 

-- Remove a transactional publication.
USE [AdventureWorks]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish', 
  @value = N'false';
GO

This example shows how to remove a merge publication and disable merge publishing for a database. This example assumes that all subscriptions were previously removed. For more information, see How to: Delete a Pull Subscription (Replication Transact-SQL Programming) or How to: Delete a Push Subscription (Replication Transact-SQL Programming).

DECLARE @publication AS sysname
DECLARE @publicationDB    AS sysname
SET @publication = N'AdvWorksSalesOrdersMerge' 
SET @publicationDB = N'AdventureWorks'

-- Remove the merge publication.
USE [AdventureWorks]
EXEC sp_dropmergepublication @publication = @publication;

-- Remove replication objects from the database.
USE master
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'merge publish', 
  @value = N'false'
GO

請參閱

工作

How to: Delete a Publication (RMO Programming)

概念

Programming Replication Using System Stored Procedures

其他資源

如何：刪除發行集 (SQL Server Management Studio)
移除複寫

說明及資訊

取得 SQL Server 2005 協助