Post reply

Find where stored procedures are used?

  • July 19, 2006 at 4:55 am

    #366793

    Hi there,

    Does anyone know how I could check all of the stored procedures within any given database to see when they were last executed?

    Just want a safe way to remove redundant procedures and tidy up the database.

    Cheers

  • July 19, 2006 at 6:07 am

    #650075

    The best way that I know is to use a trace to capture all the executions, but it costs you know

  • July 20, 2006 at 6:32 am

    #650357

    yeah thats how i was going to try doing it. was just wondering if there were any hidden functions that could find the last execute date etc..

    cheers for your reply

  • July 21, 2006 at 9:13 am

    #650743

    I assume that you are only looking at user stored procedures and don't plan to touch the system sp's.

    You could use profiler for a limited time to identity a good number of the sp's that are being used.

    A 2nd thing you could do would be add a line of code to the remaining stored procedures that writes an entry in a table that includes the date the proc was run and who ran it.

    After a reasonable time period (a week or month), rename the unused sp's and see if it causes a problem.  If you have any development staff to contact, you might send an e-mail letting them know what you are planning and request feed back if this will cause them any problems.  (Do the sp's have any internal documentation as to who created them?)

    Eventually you can get rid of the historical ones.  Before you do, I would script them out and save them as a text file somewhere. There may be sp's that are only run once a year and it would be nice to recreate them quickly if needed.

    Steve

Viewing 4 posts - 1 through 3 (of 3 total)

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