Edit

Share via

sp_droppublication (Transact-SQL)

  • Article
  • 11/29/2023

Applies to: SQL Server Azure SQL Managed Instance

Drops a publication and its associated Snapshot Agent. All subscriptions must be dropped before dropping a publication. The articles in the publication are dropped automatically. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL syntax conventions

Syntax

sp_droppublication
    [ @publication = ] N'publication'
    [ , [ @ignore_distributor = ] ignore_distributor ]
    [ , [ @publisher = ] N'publisher' ]
    [ , [ @from_backup = ] from_backup ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication to be dropped. @publication is sysname, with no default. If all is specified, all publications are dropped from the publication database, except for publications with subscriptions.

[ @ignore_distributor = ] ignore_distributor

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @publisher = ] N'publisher'

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @from_backup = ] from_backup

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return code values

0 (success) or 1 (failure).

Remarks

sp_droppublication is used in snapshot replication and transactional replication.

sp_droppublication recursively drops all articles associated with a publication and then drops the publication itself. A publication can't be removed if it's one or more subscriptions to it. For information about how to remove subscriptions, see Delete a Push Subscription and Delete a Pull Subscription.

Executing sp_droppublication to drop a publication doesn't remove published objects from the publication database or the corresponding objects from the subscription database. Use DROP <object> to remove these objects manually if necessary.

Permissions

Only members of the sysadmin fixed server role can execute sp_droppublication.

Examples

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

-- Remove a transactional publication.
USE [AdventureWorks2022]
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

Related content