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.
Note
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
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.
Note
Specifying a value of 1 for @force may leave replication-related publishing objects in the database.
(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.
(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
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.
Note
Specifying a value of 1 for @force may leave replication-related publishing objects in the database.
(Optional) If this database has no other publications, execute sp_replicationdboption (Transact-SQL) to disable publication of the current database using merge replication.
(Optional) At the Subscriber on the subscription database, execute sp_mergesubscription_cleanup (Transact-SQL) to remove any remaining replication metadata in the subscription database.
Example
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'AdventureWorks2008R2';
SET @publication = N'AdvWorksProductTran';
-- Remove a transactional publication.
USE [AdventureWorks2008R2]
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'AdventureWorks2008R2'
-- Remove the merge publication.
USE [AdventureWorks2008R2]
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