Post reply

Stored procedures

  • August 22, 2002 at 10:48 pm

    #79279

    Hi,

    I have a database which has 700 stored procedures but all of them are not being used. How do I know which procedures are not being used?

    Thanks.

  • August 23, 2002 at 12:02 am

    #435153

    Hi,

    though the method I'am going to suggest is CRUDE but it can work correct for u

    rename the SP's with some fixed character, so when name is changed surely somethings will not work then u will come to know which is that SP. . u just rename back that SP to its original name...u need to use "fixed character" so that u can programatically rename and name it back properly...to its original name in bulk. Do this on weekly taking 100 SP at a time for task

  • August 23, 2002 at 3:15 am

    #435163

    Maybe a bit less crude.

    Use profiler to log all SP:Starting events for some time.

    You can write to table or import the file later on, and do a 'simple' join on the sysobjects table to check which SP's have not been used.

    The advantage here is that normal operation is not interrupted. The disadvantage (remains), that you will not catch any procedures that are not used often.

  • August 23, 2002 at 5:27 am

    #435181

    Third option is to add logging code to each proc. With 700 procs it would be worth writing some code to add it for you.

    Im not opposed to method #1. It is brute force, but if you can run a few tests afterward pretty easy to see if everything still works and if something breaks then or later, you've got the code right there.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • August 23, 2002 at 6:01 am

    #435191

    Andy,

    Could you elaborate on 'logging code to each proc'?.

    I could not get what you mean by logging?

    Thanks.

  • August 23, 2002 at 7:18 am

    #435198

    Logging means have it write a record to a table that the SP was used and at what time.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • August 24, 2002 at 6:27 pm

    #435295

    Another twist on that could be to rename each SP as suggested in #1. Then create another SP of the same name that runs the logging code that Andy mentioned, then executes the original SP. That way you do not have to make changes to the code of your existing procs and nothing breaks for the users.

Viewing 7 posts - 1 through 6 (of 6 total)

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