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