Post reply

How to drop an SP using Tsql

  • November 11, 2019 at 6:57 pm

    #3696832

    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 ;

  • November 12, 2019 at 7:10 pm

    #3697147

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

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

  • November 12, 2019 at 9:37 pm

    #3697182

    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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 12, 2019 at 9:45 pm

    #3697185

    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 3 (of 3 total)

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