Remove or drop all stored procedures from database

  MSSQLSERVER   Thursday, April 23, 2015   4596 | No Comments Yet!!! | No Download | No Demo | pawan
 Tags: 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 

 

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.

 

Comments: