DROP PROCEDURE (Transact-SQL)

  • Article

Removes one or more stored procedures or procedure groups from the current database in SQL Server 2012.

Topic link icon Transact-SQL Syntax Conventions

Syntax

DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]

Arguments

  • 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;

See Also

Reference

ALTER PROCEDURE (Transact-SQL)

CREATE PROCEDURE (Transact-SQL)

sys.objects (Transact-SQL)

sys.sql_modules (Transact-SQL)

Concepts

Delete a Stored Procedure