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!")

Comments

  1. This is an awesome codesnippet! Thanks!

    ReplyDelete
  2. Anonymous4:57 AM

    Cheers for that one, save me some extra time.

    ReplyDelete
  3. Anonymous6:35 AM

    Awesome!

    ReplyDelete
  4. It is good but it is not perfect because there is a problem if you have a schema (not dbo) with the stored procedure.

    The sys.procedures doesn't give the name of the shema but only the schema_id, so I do that :

    Select 'Drop Procedure ' + s.Name + '.' + p.Name
    from sys.procedures p
    INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
    Where [type] = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'

    But there is an other solution : to create a stored proc to do the work :

    create Procedure dbo.DeleteAllProcedures
    As
    declare @schemaName varchar(500)
    declare @procName varchar(500)

    declare cur cursor
    for SELECT s.Name, p.Name
    FROM sys.procedures p
    INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
    WHERE p.type = 'P' and is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'
    ORDER BY s.Name, p.Name
    open cur

    fetch next from cur into @schemaName, @procName
    while @@fetch_status = 0
    begin
    if @procName <> 'DeleteAllProcedures'
    exec('drop procedure ' + @schemaName + '.' + @procName)
    fetch next from cur into @schemaName, @procName
    end
    close cur
    deallocate cur
    GO

    The 'order by' is not useful, I put here for test.
    We can use first :
    select 'drop procedure ' + @schemaName + '.' + @procName
    before to replace by :
    exec('drop procedure ' + @schemaName + '.' + @procName)
    to be sure that we drop only the procedures we want to drop.

    Cheers,

    Dominique Gratpain

    ReplyDelete

Post a Comment

Popular posts from this blog

FIREFOX / IE Word-Wrap, Word-Break, TABLES FIX

Some observations on Script Callbacks, "AJAX", "ATLAS" "AHAB" and where it's all going.

ASP.NET "App_Data": Writing files vs Application Restarts