mattberther.com » Drop all stored procedures

Drop all stored procedures

Posted December 15th, 2004 by Matt Berther

Ever want to drop all of the stored procedures in a database? I typically use this technique to do database updates (drop them all, and then recreate them) to make sure that I dont have any left over procedures and so I dont have to maintain different scripts for creating and updating my databases.

Try this script…

USE myDatabase
GO
 
declare @procName sysname
 
declare someCursor cursor FOR
    SELECT name FROM sysobjects WHERE type = 'P' AND objectproperty(id, 'IsMSShipped') = 0
 
open someCursor
fetch next FROM someCursor INTO @procName
while @@FETCH_STATUS = 0
begin
    exec('drop proc ' + @procName)
    fetch next FROM someCursor INTO @procName
end
 
close someCursor
deallocate someCursor
go

For the love of God, make sure that you dont run this against your master database. :)

Update: Changed declaration of @procName to sysname as per Raymond’s comment.

6 Comments

This entry was posted on Wednesday, December 15th, 2004 at 4:08 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Raymond Lewallen

December 16th, 2004

This is a petty comment, but here it is anyways :)

Object names in Sql Server are stored as Unicode. Specifically, object names are stored using the UDT “sysname”, which is the equivalent of NVARCHAR(128). So no need to declare any variable longer than 128 characters, because the name won’t exist at a length greather than that in sysobjects. Also, varchar() is non-unicode, whereas nvarchar (and sysname for that matter) are unicode data types. I can’t remember off the top of my head where I had a problem with this in the past, but I was doing a convert to varchar from sysname and ended up with unexpected results, so use nvarchar, or better yet sysname because it enforces the 128 character limit, just to be safe. Chances are, it won’t make a hill of beans difference though. I’m just bored and wanted to post something.

Hassan

June 6th, 2007

Thanks a lot…

Abhilash

July 29th, 2009

Nice Post and very useful!

Simon

August 10th, 2009

Fab script – saved me a good deal of trouble. One minor amend – some of my upsized procedures have dashes in the title so I made a minor change to the exec statement within the begin…end loop to add quotes around the SP name:

exec(’drop proc ‘ + ‘”‘ + @procName + ‘”‘)

Thanks again Matt. Still useful, 5 years after it was published :-)

Pedrero

October 20th, 2009

Thank you man

Amr ElGarhy

December 15th, 2009

Helped me too much, nice script, did my job in no time :)