Skip to content

drop-procedure-transact-sql.md

Latest commit

 

History

History
108 lines (81 loc) · 4.43 KB

drop-procedure-transact-sql.md

File metadata and controls

108 lines (81 loc) · 4.43 KB

title: "DROP PROCEDURE (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "05/11/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.reviewer: "" ms.suite: "sql" ms.technology: t-sql ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:

  • "DROP PROCEDURE"
  • "DROP_PROCEDURE_TSQL" dev_langs:
  • "TSQL" helpviewer_keywords:
  • "removing stored procedures"
  • "dropping procedure groups"
  • "deleting stored procedures"
  • "deleting procedure groups"
  • "DROP PROCEDURE statement"
  • "dropping stored procedures"
  • "stored procedures [SQL Server], removing"
  • "removing procedure groups" ms.assetid: 1c2d7235-7b9b-4336-8f17-429e7d82c2c3 caps.latest.revision: 51 author: CarlRabeler ms.author: carlrab manager: craigg monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017"

DROP PROCEDURE (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

Removes one or more stored procedures or procedure groups from the current database in [!INCLUDEssCurrent].

Topic link icon Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure_name }

Arguments

IF EXISTS
Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] through current version).

Conditionally drops the procedure only if it already exists.

schema_name
The name of the schema to which the procedure belongs. A server name or database name cannot be specified.

procedure
The name of the stored procedure or stored procedure group to be removed. Individual procedures within a numbered procedure group cannot be dropped; the whole procedure group is dropped.

Best Practices

Before removing any stored procedure, check for dependent objects and modify these objects accordingly. Dropping a stored procedure can cause dependent objects and scripts to fail when these objects are not updated. For more information, see View the Dependencies of a Stored Procedure

Metadata

To display a list of existing procedures, query the sys.objects catalog view. To display the procedure definition, query the sys.sql_modules catalog view.

Security

Permissions

Requires CONTROL permission on the procedure, or ALTER permission on the schema to which the procedure belongs, or membership in the db_ddladmin fixed server role.

Examples

The following example removes the dbo.uspMyProc stored procedure in the current database.

DROP PROCEDURE dbo.uspMyProc;  
GO

The following example removes several stored procedures in the current database.

DROP PROCEDURE dbo.uspGetSalesbyMonth, dbo.uspUpdateSalesQuotes, dbo.uspGetSalesByYear;

The following example removes the dbo.uspMyProc stored procedure if it exists but does not cause an error if the procedure does not exist. This syntax is new in [!INCLUDEssSQL15].

DROP PROCEDURE IF EXISTS dbo.uspMyProc;  
GO

See Also

ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
sys.objects (Transact-SQL)
sys.sql_modules (Transact-SQL)
Delete a Stored Procedure