Chapter 1: Commands

drop procedure

Removes a procedure.

drop proc[edure] [owner.]procedure_name 
	[, [owner.]procedure_name] ...

procedure_name: is the name of the Transact-SQL or SQLJ procedure to drop. Specify the owner’s name to drop a procedure of the same name owned by a different user in the current database. The default value for owner is the current user.

Deletes the stored procedure showind:

drop procedure showind

Unregisters the extended stored procedure xp_echo:

drop procedure xp_echo

drop procedure drops user-defined stored procedures, system procedures, and extended stored procedures (ESPs).
Adaptive Server checks the existence of a procedure each time a user or a program executes that procedure.
A procedure group (more than one procedure with the same name but with different number suffixes) can be dropped with a single drop procedure statement. For example, if the procedures used with the application named orders were named orderproc;1, orderproc;2, and so on, the following statement drops the entire group:
```
drop proc orderproc
```
Once procedures have been grouped, individual procedures within the group cannot be dropped. For example, the following statement is not allowed:
```
drop procedure orderproc;2
```
You cannot drop extended stored procedures as a procedure group.
sp_helptext displays the procedure’s text, which is stored in syscomments.
sp_helpextendedproc displays ESPs and their corresponding DLLs.
Dropping an ESP unregisters the procedure by removing it from the system tables. It has no effect on the underlying DLL.
drop procedure drops only user-created procedures from your current database.

ANSI SQL – Compliance level: Transact-SQL extension.

drop procedure permission defaults to the procedure owner and is not transferable.

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
28	drop	drop procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if set proxy in effect