Post reply

Need to know last date\time a table was updated

  • April 9, 2009 at 4:39 pm

    #202912

    I need to know when a table (actually several tables) last had data INSERTed, DELETEd, or Modified.

    Any ideas much appreciated.

    TIA,

    barkingdog

    P.S "modified_date" in sys.tables only refers to when a table had its schema changed. Not what I want.

    I found that modified_date in sys.tables only is updates

    MODIFIed

  • April 9, 2009 at 6:10 pm

    #975518

    There are a few different methods each with its own set of advantages.

    The way I primarily accomplish this task is by adding ModifiedUser, ModifiedDate, CreatedUser, CreatedDate fields to the table and whenever I insert, add, or delete...then update these fields. This is probably the most efficient (performance wise) method, but of course you need to be able to modify the tables and code.

    There is also a SQL Server Trace that you can turn on to log many types of events...this effects performance as you have to keep it running if you want to monitor the database all the time.

    SQL Server also offers something called C2 logging (or Auditing). The basics of this can be found here:

    http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

    Hope this helps...

Viewing 2 posts - 1 through 1 (of 1 total)

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