Removes a procedure.
drop proc[edure] [owner.]procedure_name [, [owner.]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 stored procedures from your current database.
ANSI SQL – Compliance level: Transact-SQL extension.
The permission checks for drop procedure differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be the procedure owner or a user with drop any procedure privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be the procedure owner or a user with sa_role. |
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
28 |
drop |
drop procedure |
|
Commands create procedure, create procedure (SQLJ)
System procedures sp_depends, sp_dropextendedproc, sp_helpextendedproc, sp_helptext, sp_rename
