Category >
MSSQLSERVER
|| Published on :
Thursday, April 23, 2015 || Views:
8442
||
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