Remove or drop all stored procedures from database

Category > MSSQLSERVER || Published on : Thursday, April 23, 2015 || Views: 5963 || 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
-- http://www.sourcecodehub.com

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 
Begin 
 Exec('drop procedure ' + @procName) 
 Fetch Next From cur Into @procName 
End
Close cur 
Deallocate cur