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

    Unanswered: how to check when a database was last updated

    Is there any way to query a database and figure out when any table within that database was last updated.

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    do you mean when the data was last updated or when table structures were last updated?
    Johan

  3. #3
    Join Date
    Aug 2003
    Posts
    3
    Originally posted by jora
    do you mean when the data was last updated or when table structures were last updated?
    When the data was last updated.

    Ideally I am looking for timestamp information like, the Northwind database was last updated (ie. a row was changed in the Customers table) on 1/1/2003.

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    as far as I know, SQL does only keep track of creation dates of tables and not for data modification. Therefore I think you should use a last_modified column of datetime and triggers to update this column.
    Johan

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A modified_datetime field (which you will need to update using a trigger on each table) is the preferred method. I usually store two values; the modified date and the modifier, which I pull from one of the system user nyladic functions.

    If you can't modify the database, there is probably a way to do it (perhaps through a third-party application) by monitoring the log.

    blindman

  6. #6
    Join Date
    Aug 2003
    Posts
    3

    Talking

    You are right, I am not able to modify the tables to add any new columns, but I was able to find a workable solution.

    The ::fn_dblog(null, null) returned exactly the data I was looking for. Check out this link: http://www.novicksoftware.com/UDFofW...-fn_dblog.htm, for more info about it.

    Thanks everyone for the help.

    Matt

Posting Permissions

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