DROP PROCEDURE (T-SQL)

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 defaults to the procedure owner and is not transferable. However, members of the db_owner and sysadmin fixed database roles 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