Dropping All Stored Procedure in Database in SQL Server 2012

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 [namefrom 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 [namefrom 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.