DROP PROCEDURE

Transact-SQL Reference

Transact-SQL Reference

DROP PROCEDURE

Removes one or more stored procedures or procedure groups from the current database.

Syntax

DROP PROCEDURE { procedure } [ ,...n ]

Arguments

procedure

Is name of the stored procedure or stored procedure group to be removed. Procedure names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the procedure owner name is optional, and a server name or database name cannot be specified.

n

Is a placeholder indicating that multiple procedures can be specified.

Remarks

To see a list of procedure names, use sp_help. To display the procedure definition (which is stored in the syscomments system table), use sp_helptext. When a stored procedure is dropped, information about the procedure is removed from the sysobjects and syscomments system tables.

Individual procedures in the group cannot be dropped; the entire procedure group is dropped.

User-defined system procedures (prefixed with sp_) are dropped from the master database whether or not it is the current database. If the system procedure is not found in the current database, Microsoft® SQL Server™ tries to drop it from the master database.

Permissions

DROP PROCEDURE permissions default to the procedure owner and are not transferable. However, members of the db_owner and db_ddladmin fixed database roles and the sysadmin fixed server role can drop any object by specifying the owner in DROP PROCEDURE.

Examples

This example removes the byroyalty stored procedure (in the current database).

DROP PROCEDURE byroyalty
GO

See Also

ALTER PROCEDURE

CREATE PROCEDURE

sp_depends

sp_helptext

sp_rename

syscomments

sysobjects

USE