Last Modified Dates on Database & Tables?

  • Hello,

    I'm sure this is an easy one for all you knowledgeable people out there.

    How can I find the modification date of a database or table?

    BTW, by modification date, I mean the last time it was updated or changed - not just when the table structure was modified as seems to be the case in sys.tables.

    Thanks.

    Steve

  • raotor (12/10/2012)


    Hello,

    I'm sure this is an easy one for all you knowledgeable people out there.

    How can I find the modification date of a database or table?

    BTW, by modification date, I mean the last time it was updated or changed - not just when the table structure was modified as seems to be the case in sys.tables.

    Thanks.

    Steve

    you can infer the last time (since the server was restarted) the last time a table was selected or updated by looking at the indexes

    you cannot find out the information on a per row basis.

    if you need that level of auditing, you can use some of the many functionalities in SQL Server: some examples(all of which must be set up prior to data being changed):

    SQL Audit, CDC, C2 Level Auditng, Add your own trace, exrtended events, DML triggers on specific tables,

    for last touched index info on a table that i mentioned , here's a code example:

    --based on the ideas from

    --http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

    ;WITH ServerStarted AS

    (

    SELECT

    MIN(last_user_seek) AS first_seek,

    MIN(last_user_scan) AS first_scan,

    MIN(last_user_lookup) AS first_lookup

    FROM sys.dm_db_index_usage_stats

    ),

    ServerFirst AS

    (

    SELECT

    CASE

    WHEN first_seek < first_scan AND first_seek < first_lookup

    THEN first_seek

    WHEN first_scan < first_seek AND first_scan < first_lookup

    THEN first_scan

    ELSE first_lookup

    END AS usage_start_date

    FROM ServerStarted

    ),

    myCTE AS

    (

    SELECT

    DB_NAME(database_id) AS TheDatabase,

    OBJECT_NAME(object_id,database_id) As TheTableName,

    last_user_seek,

    last_user_scan,

    last_user_lookup,

    last_user_update

    FROM sys.dm_db_index_usage_stats

    )

    SELECT

    MIN(ServerFirst.usage_start_date) AS usage_start_date,

    x.TheDatabase,

    x.TheTableName,

    MAX(x.last_read) AS last_read,

    MAX(x.last_write) AS last_write

    FROM

    (

    SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE

    UNION ALL

    SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE

    ) AS x

    CROSS JOIN ServerFirst

    GROUP BY TheDatabase,TheTableName

    ORDER BY TheDatabase,TheTableName

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Ohhh errr 🙂

    Thanks for that. I'd hoped that SQL server had some simple DMV that would provide this information, but no matter.

    So, no indexes, no way of finding the time a table was last updated.

    Thanks for the code samples.

  • There are some other ways to get to record level, such as adding a field with a default value of GETDATE(), and/or adding an AFTER INSERT, or AFTER UPDATE trigger (or both), but such things can carry some level of performance hit if the insert or update volume is high enough, so they're not always the right way to go. Once again, the answer is: "it depends". It's usually a question of performance vs. the need for the information.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Indeed, plenty of food for thought there.

    Thanks once again for all your kind help.

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

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