| 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.technology | t-sql | ||||||||
| ms.topic | language-reference | ||||||||
| f1_keywords |
|
||||||||
| dev_langs |
|
||||||||
| helpviewer_keywords |
|
||||||||
| ms.assetid | 1c2d7235-7b9b-4336-8f17-429e7d82c2c3 | ||||||||
| 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||=azuresqldb-mi-current |
[!INCLUDEtsql-appliesto-ss2008-all-md]
Removes one or more stored procedures or procedure groups from the current database in [!INCLUDEssCurrent].
Transact-SQL Syntax Conventions
-- 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 } 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.
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
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.
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.
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
ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
sys.objects (Transact-SQL)
sys.sql_modules (Transact-SQL)
Delete a Stored Procedure