How to drop an SP using Tsql

  • sizal0234

    SSCrazy

    Points: 2064

    Hi,

    What approach would you take if you have to delete multiple SP's from a database which are not required anymore. So, one of the option is you can go to DB - Search for that SP under specific folder - and right click and delete. However that is time consuming for each SP even with Filter settings. So,

    Please share any best practise here and let me know if below works?

    USE DBNAME

    DROP PROCEDURE dbo.test, dbo.utest, dbo.ytest ;

  • Site Owners

    SSC Guru

    Points: 80379

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jeff Moden

    SSC Guru

    Points: 995117

    First of all, I never drop a supposedly unused stored procedure immediately.  I rename the stored procedures by appending the with "_ToBeDeleted"  Such a rename constitutes a modification of the proc and so I'll delete such renamed stored procedures after "x" number of days.

    As for identifying the procs to be dropped, you have a list of stored procedure names, correct?  Just import that list to a table and write the code to generate the necessary commands.  Actually, I use a stored procedure for such a thing because I do it often enough with the only difference being that I do this same thing for tables.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • sizal0234

    SSCrazy

    Points: 2064

    As a precaution, I always confirm before deletion and also script the SP's and save them on separate drive for couple of days so that I can recreate them if needed.  YES, I do have a confirmed list of SP's to be removed.

    The reason for deleting was it is a specific testing environment where that SP's should not be there after couple of testing cycles.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply