Remove or drop all stored procedures from database

3 Comments | 4368 Views | Post by: Admin | Tags: drop all stored procedures,Remove all stored procedures, tips stored procedures, mssql server, sql server tips

Sometimes, there is a case, when we need to remove all stored procedure completely from the database.

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 


Post Comments

if you have some suggestion, question, request on Remove or drop all stored procedures from database.

Please use the comment box to express your views.