Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    3

    Unanswered: When a sproc or table was Last Used

    How can I determine when a sproc or table was last used?
    I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure??
    Thanks,
    DL

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't do this without placing a piece of code at the beginning of the sproc to log it's activity in an event table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And for the tables you can put a trigger on everyone and write to a log....

    And so waht if a table or sproc is used infrequently?

    You might still need it.

    Oh, btw, this is what change control is for.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Aug 2005
    Posts
    3
    Blindman,
    Thank you. That's an excellent idea.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ..but Brett's point is valid too. Some procedures may only be used once a year or so, especially those dealing with annual financial reporting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I usually grab the datetime on entry of the sproc, then on exit (after the rollback or commit, I write to the log. I insert you the user was, the time of the entry, the length of the transaction, the SQL Cond code (in SQL Server I would use @@ERROR)..and any other useful info you want to track.

    You need to make sure it's after you handle any transactions...becuse if an error occurs, and you do it up front, and you need to rollback, the log entry will be rolled back as well...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •