14May
How to Delete All Stored Procedures From An SQL Server Database?
Here is a way to delete all stored procedures from an SQL Server database
DECLARE @procedureName varchar(500) DECLARE cur CURSOR FOR SELECT [name] FROM sys.objects WHERE type = 'p' OPEN cur FETCH NEXT FROM cur INTO @procedureName WHILE @@fetch_status = 0 BEGIN EXEC('DROP PROCEDURE ' + @procedureName) FETCH NEXT FROM cur INTO @procedureName END CLOSE cur DEALLOCATE cur
20 Responses to How to Delete All Stored Procedures From An SQL Server Database?
hjhjhgjhgj
October 9th, 2009 at 11:07 am
jghjghj
Anonymous
December 30th, 2009 at 11:20 pm
Absolutely neat code Thanks a Lot you saved me a hell of time
lepham
January 27th, 2010 at 8:40 pm
Thank you very much, useful with me.
SQLRocker
May 24th, 2010 at 10:11 am
Neat Code, Saved me time too!! Thx!
archana
June 3rd, 2010 at 4:21 am
hey thank you so much yaar
Irfan Ahmad
March 8th, 2011 at 10:33 am
Thanks a lot, it saved my 3 hour job. Once again thanks a lot
Amit
March 28th, 2011 at 8:26 pm
EXEC sp_MSforeachtable @command1 = “DROP PROCEDURE ?”
everestto
April 22nd, 2011 at 10:03 am
Fantastic code. Worked perfectly well. Didn’t seem affect system stored procedures which was what I expected. I hope it didn’t drop system procedures?
Suneetha
June 27th, 2011 at 10:37 pm
Nice and Time saving code
Channabasu sarvi
July 26th, 2011 at 12:47 am
Smart code,its really helped me alot for delete all store procedure within short time, thanks
cha man
August 19th, 2011 at 8:20 am
thanks a lot for sharing this code.. it made my life so much easier
abi
November 19th, 2011 at 7:16 am
super nice very usefull and save time thanks
Rumesh Srivastav
December 23rd, 2011 at 2:06 am
Check this helpful link too…
http://mindstick.com/Articles/79be36ec-d4e6-4ef5-ae5d-c65fd7ede2b9/?Stored%20Procedure%20in%20SQL
Its also having a wonderful explanation on stored procedure in sql server. which helped me lot.
Thanks
ashish
January 20th, 2012 at 2:50 am
Check out following link this will allow you to delete all tables, all relations ships, all views functions
etc
http://aspdotnetmatters.blogspot.com/search/label/MSSQL%202008
Ansari
January 30th, 2012 at 11:06 pm
Good work
sangram
March 10th, 2012 at 12:29 am
Here is similar stuff that might of interest to you
http://msdotnetbuddy.blogspot.in/2010/07/dropping-all-stored-procedure-from.html
Frances Palafox
March 10th, 2012 at 10:18 am
Hell, there are no rules here - we’re trying to accomplish something.
You can create a buck. It’s a lot tougher to make a difference.
neeraj
April 11th, 2012 at 11:42 pm
nice code but how to use them i got en error while using this.
ANKIT
April 2nd, 2013 at 11:59 am
I would like to add ‘[' at the begining of that variable and ']‘ at the end since some procedure may contain version number as i did, like sp_something_version1.1 so at that time you will get an error which says ‘incorrect syntex near ‘.1′.
so something like this,
DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
FOR SELECT [name] FROM sys.objects WHERE type = ‘p’
OPEN cur
FETCH NEXT FROM cur INTO @procedureName
WHILE @@fetch_status = 0
BEGIN
EXEC(’DROP PROCEDURE ‘ + ‘['+@procedureName+']‘)
FETCH NEXT FROM cur INTO @procedureName
END
CLOSE cur
DEALLOCATE cur
Uthaya
May 8th, 2013 at 7:35 am
Thanks