Removes user-created stored procedures from the current database.
DROP PROCedure [owner.]procedure_name [, [owner.]procedure_name...]
where
DROP PROCEDURE removes user-created procedures from the user's current database. User-defined system procedures (prefixed with sp_) are dropped from the master database whether or not it is the current database. If the procedure is not found in the current database, SQL Server tries to drop it from the master database. When you drop a stored procedure, information about the procedure is removed from the sysobjects, sysprocedures, and syscomments system tables.
To see a list of procedure names, use the sp_help system stored procedure. To display the procedure definition (which is stored in the syscomments system table), use the sp_helptext system stored procedure.
A procedure group (more than one procedure with the same name but with different ;number suffixes) can be dropped using a single DROP PROCEDURE statement. For example, if the procedures used with the application orders are named orderproc;1, orderproc;2, and so on, the "DROP PROC orderproc" statement drops the entire group. Once procedures have been grouped, individual procedures within the group cannot be dropped. For example, "DROP PROCEDURE orderproc;2" is not allowed.
DROP PROCEDURE permission defaults to the procedure owner and is not transferable. However, the database owner and system administrator can drop any object by specifying the owner in the DROP PROCEDURE statement.
This example removes the stored procedure byroyalty.
DROP PROCEDURE byroyalty
| CREATE PROCEDURE | sp_helptext |
| sp_depends | sp_rename |