Stored procedures

  • kmsonti

    Old Hand

    Points: 361

    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.

  • gvphubli

    Old Hand

    Points: 361

    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

  • NPeeters

    SSChampion

    Points: 12220

    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.

  • Andy Warren

    SSC Guru

    Points: 119694

    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/

  • kmsonti

    Old Hand

    Points: 361

    Andy,

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

    I could not get what you mean by logging?

    Thanks.

  • Antares686

    SSC Guru

    Points: 125444

    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)

  • cwedgwood50

    SSCommitted

    Points: 1751

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

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