Skip to content

Latest commit

 

History

History
82 lines (56 loc) · 3.25 KB

sp-droppublication-transact-sql.md

File metadata and controls

82 lines (56 loc) · 3.25 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_droppublication (Transact-SQL)
Drops a publication and its associated Snapshot Agent. This stored procedure runs at the Publisher on the publication database.
markingmyname
maghan
randolphwest
11/28/2023
sql
replication
reference
sp_droppublication_TSQL
sp_droppublication
sp_droppublication
TSQL

sp_droppublication (Transact-SQL)

[!INCLUDE SQL Server SQL MI]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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

[!INCLUDE ssinternalonly-md]

[ @publisher = ] N'publisher'

[!INCLUDE ssinternalonly-md]

[ @from_backup = ] from_backup

[!INCLUDE ssinternalonly-md]

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

:::code language="sql" source="../replication/codesnippet/tsql/sp-droppublication-trans_1.sql":::

Related content