In this step by step SQL Tutorial I will show you how to use DROP PROCEDURE statement which is used to remove stored procedure object from selected database. For the purpose of this tutorial I will use SQL Server Management Studio 2012.
You might find this tutorial if:
- You are new to SQL and DROP PROCEDURE statements
- You would like to find out how to perform DROP PROCEDURE using interface and T-SQL script
- You are preparing for Microsoft Certification and DROP is not what you have used before
SQL DROP PROCEDURE is used to permanently remove stored procedure object from database.
Below I show you how to remove stored procedure from database using SSMS interface and using T-SQL.
In the Object Explorer I open required database in this case it is AdventureWorksDW2012 then I expand Programmability, Stored Procedures folder and find procedure which I want to remove. In this example it is dbo.Test.
Below I will show you how to delete (DROP) procedure from database using Object Explorer in SSMS 2012.
I open Object Explorer, AdventureWorksDW2012 database and from Stored Procedures folder I right click dbo.Test.
From drop down box I select Delete.
In Delete Object window I click OK.
This time I will show you how to drop stored procedure using T-SQL
Create new query and make sure that correct database is selected on the database drop down box on the tool bar.
SYNTAX: DROP PROCEDURE SchemaName.ProcedureName
The syntax is very simple and i below is my sample which I executed using Execute button.
DROP PROCEDURE dbo.Test
Once i execute my drop procedure I get message that code run successfully.
The stored procedure should disapper from object explorer but if it doesn't right click stored procedure folder and select refresh.
Below is object explorer with removed (dropped) stored procedure.