Results 1 to 14 of 14

Thread: Drop procedure

  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: Drop procedure

    Is there a way to DROP several SP at one time with some sort of wildcard?

    Something like "DROP PROCEDURE sp_%"


    Thx in advance

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    select 'DROP PROCEDURE [' + SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + ']'
    from information_schema.routines 
    where routine_type = 'PROCEDURE' and
    	Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    pefect, i just delete the row that contains the procedure then...

    Thankx Wim

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mrpcguy View Post
    pefect, i just delete the row that contains the procedure then...
    Emphatically NO!

    The SELECT statement that Wim gave you will actually produce a set of DROP PROCEDURE statements that you can then copy from the result set and paste in as new commands... When you execute the commands created by the SELECT statement, those commands will actually drop the procedures.

    DO NOT EVER directly delete rows from system tables unless you clearly understand exactly what will happen, and only do that if you have a verified good backup!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2004
    Posts
    162
    i got that after my post and started to think it was a bad idea

    Thx very much for pointing that out so i diden't have to figure it out for myself in a bad way.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't get al this excitement.

    What exactly were you planning to do with my query?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think he was thinking about running a delete statement on information_schema.routines.

  8. #8
    Join Date
    Mar 2004
    Posts
    162
    Quote Originally Posted by MCrowley View Post
    I think he was thinking about running a delete statement on information_schema.routines.
    Yes that was my first idea.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    OMG.

    But I still don't get what made you want to do that.

    I said "Try this", followed by a query.

    What would make me think that one would not execute that query, but instead try to delete records from a table that is used in that query, or drop that table altogether or drop the whole database that table is in.

    Still puzzled.
    Last edited by Wim; 03-30-12 at 12:58.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Mar 2004
    Posts
    162
    Quote Originally Posted by Wim View Post
    OMG.

    But I still don't get what made you want to do that.

    I said "Try this", followed by a query.

    What would make me think that one would not execute that query, but instead try to delete records from a table that is used in that query, or drop that table altogether or drop the whole database that table is in.

    Still puzzled.
    First: I diden't do that, i was only thinking of doing that for a short period.
    Second: The thought of trying to delete something from a system table (or similar) in a production enviroment NEVER crossed my mind.
    Third: In my mind there is nothing wrong whit trying stuff you are not sure of in a safe enviroment (lab)

    Now this may not be clear in my previous post but this is the case

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good point on the lab, you can always toast a lab server (often many, many times) in the process of figuring out new things. I can't count how many VMs I "burn" in a year because I consider them as expendables.

    I have done all manor of "naughty" things with servers in the past, usually to bring them back from the dead so I can salvage the data from the "zombie" to restore onto a working (often freshly built) server. I don't do that casually, and usually only on a database/server that is stone cold dead, but I do sometimes do it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by mrpcguy View Post

    First: I diden't do that, i was only thinking of doing that for a short period.
    A period that is way too long

    Second: The thought of trying to delete something from a system table (or similar) in a production enviroment NEVER crossed my mind.
    Oh,tank god

    Third: In my mind there is nothing wrong whit trying stuff you are not sure of in a safe enviroment (lab)
    Ummm..no

    Do you have all your sprocs in change control, or at least in a folder on a backed up server?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Mar 2004
    Posts
    162
    Quote Originally Posted by Brett Kaiser View Post
    A period that is way too long



    Oh,tank god



    Ummm..no

    Do you have all your sprocs in change control, or at least in a folder on a backed up server?
    ouch, still thinking about the magic in http://www.dbforums.com/microsoft-sq...elenvance.html i see

    And yes, i have a fresh exact copy of production i try stuff in before i make changes in production.

  14. #14
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by mrpcguy View Post
    And yes, i have a fresh exact copy of production i try stuff in before i make changes in production.
    Smart guy.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •