Delete a Stored Procedure
This topic describes how to delete a stored procedure in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.
Before you begin: Limitations and Restrictions, Security
To delete a procedure, using: SQL Server Management Studio, Transact-SQL
Before You Begin
Limitations and Restrictions
Deleting a procedure can cause dependent objects and scripts to fail when the objects and scripts are not updated to reflect the removal of the procedure. However, if a new procedure of the same name and the same parameters is created to replace the one that was deleted, other objects that reference it will still process successfully. For more information, see View the Dependencies of a Stored Procedure.
Security
Permissions
Requires ALTER permission on the schema to which the procedure belongs, or CONTROL permission on the procedure.
How to Delete a Stored Procedure
You can use one of the following:
SQL Server Management Studio
Transact-SQL
Using SQL Server Management Studio
To delete a procedure in Object Explorer
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
Expand Stored Procedures, right-click the procedure to remove, and then click Delete.
To view objects that depend on the procedure, click Show Dependencies.
Confirm the correct procedure is selected, and then click OK.
Remove references to the procedure from any dependent objects and scripts.
[Top]
Using Transact-SQL
To delete a procedure in Query Editor
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs, or, from the tool bar, select the database from the list of available databases.
On the File menu, click New Query.
Obtain the name of stored procedure to remove in the current database. From Object Explorer, expand Programmability and then expand Stored Procedures. Alternatively, in the query editor, run the following statement.
SELECT name AS procedure_name ,SCHEMA_NAME(schema_id) AS schema_name ,type_desc ,create_date ,modify_date FROM sys.procedures;Copy and paste the following example into the query editor and insert a stored procedure name to delete from the current database.
DROP PROCEDURE <stored procedure name>; GORemove references to the procedure from any dependent objects and scripts.
[Top]