How to drop an SP using Tsql

  • sizal0234


    Points: 2187


    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?


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

  • Site Owners

    SSC Guru

    Points: 80376

    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: 996636

    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.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • sizal0234


    Points: 2187

    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