Introduction
In this article, I describe how to drop all the Stored Procedures in a particular database. Several times in our project we needed to drop all Stored Procedures. If there is a large number of Stored Procedures then it is very tedious to drop all of them. Here I explain how to drop all Stored Procedures in a database automatically.
I assume you have a basic knowledge of Stored Procedures and cursors; for more help, you can visit:

First of all we create a table.

Creation of Table

  1. create table emp(empId int, empName varchar(15))
  2. go
  3. insert into emp
  4. select 1,'Deepak'union all
  5. select 2,'Arora'
  6. go
  7. select * from emp
Output
delete-all-stored-procedure.png
Now we create two Stored Procedures.
Creation of the first Stored Procedure
  1. create proc usp_select
  2. as
  3. select * from emp
  4. go
  5. exec usp_select
Output
delete-all-stored-procedure.png
Creation of the second Stored Procedure
  1. create proc usp_insert(@id int, @name varchar(15))
  2. as
  3. insert into emp values (@id, @name)
  4. go
  5. exec usp_insert 3,'Daljeet singh'
  6. go
  7. exec usp_select
Output
delete-all-stored-procedure-2012.png

Now we create a Cursor
  1. declare cur_dropProc cursor
  2. scroll for
  3. select [name] from sysobjects where xtype='p'
Now run the following code
  1. open cur_dropProc
  2. go
  3. Declare @procName varchar(500)
  4. fetch first from cur_dropProc into @procName
  5. while @@fetch_status=0
  6. begin
  7. Exec('drop procedure ' + @procName)
  8. fetch next from cur_dropProc into @procName
  9. end
  10. go
  11. close cur_dropProc
  12. go
  13. deallocate cur_dropProc
Output
delete-all-stored-procedure-.png
Now see the Stored Procedure in the Database
  1. select [name] from sysobjects where xtype='p'
Output
delete-all-stored-proceduree.png
Summary
In this article, I described how to drop all the Stored Procedures in a database in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.