Stored procedures

  • 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.

  • 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

  • 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.

  • 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,

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

    I could not get what you mean by logging?

    Thanks.

  • 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)

  • 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