Remove or drop all stored procedures from database

Category > MSSQLSERVER || Published on : Thursday, April 23, 2015 || Views: 8563 || drop all stored procedures Remove all stored procedures tips stored procedures mssql server sql server tips

If we have hundreds of stored procedure in our database, to remove these, completely from database became a lengthy task. In this article, I would like to  share the script by which you we can remove stored procedure completely from database.

Step1: Open the MS-SQL Managment Studio(in my  case MSSQL 2008 R2)

Step 2: Select you database from you want to remove all the Store Procedure.

Step 3: Run the below script content

 -- drop all user defined stored procedures

Declare @procName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'p' 
Open cur 
Fetch Next From cur Into @procName 
While @@fetch_status = 0 
 Exec('drop procedure ' + @procName) 
 Fetch Next From cur Into @procName 
Close cur 
Deallocate cur