delete stored procs

Posted on 2012-08-27
Last Modified: 2012-08-27
I need some SQL to delete a bunch (300) of stored procs.  The names of the procs all begin with the same string.  

Thanks in advance
Question by:HLRosenberger
    LVL 7

    Expert Comment

    Open a cursor that collects all the procs beginning with that string.

    Then use dynamic SQL to execute a 'drop proc' command for each.
    LVL 142

    Accepted Solution

    to start with, check this query:
    SELECT 'DROP PROCEDURE ' + name + ';'
    FROM sys.procedures
    where [name] like 'abc%' 

    Open in new window

    copy/paste that output into a sql command window and run it
    LVL 2

    Expert Comment

    declare @name varchar(4000)
    declare c cursor for
    select name from sysobjects where xtype='p' and name like '%sp_'
    open c
    fetch next from c into @name
    while @@fetch_status=0
    print @name
    --your drop statement

    fetch next from c into @name
    close c
    deallocate c
    LVL 1

    Author Closing Comment

    Terrific!  Thanks!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now