4/07/2007

How to Delete All the Stored Procedures in a SQL Server 2005 Database

I've been going through the learning curve with CodeSmith and the initial results are pretty interesting, especially with the free NetTiers template set. Man, does that generate a bunch of stored procs, entity and domain objects and controls! If you get used to the model, you are virtually going to get yourself an entire application in one 5-second CodeSmith "Swoop"!

One of the things I came up with while monkeying around with the Database schema was the need to delete all the generated stored procs so CodeSmith can be pointed back at a "procedure-less" database for another take.

The problem is you don't want to just select everything with type 'p' from SysObjects - because there are system procedures there such as for diagrams. Also, you cannot just do an ad-hoc delete, because you'll get an error message; Sql Server is trying to prevent you from shooting yourself in the foot.

Here is an easy way to do it:

First, right - click on the query pane and choose "Results to text".

Next, execute this script:



Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

What you'll get is a nice list of "Drop Procedure" statements that you can paste into the query editor window and execute.

VoilĂ ! All those nasty stored procs are gone. (Or as one of my redneck friends would say, "Walla! Dose procs done left the buildin' , man!")