How to know what time a table is last-updated?

  • The update can be either table structure or its data. How to know it in SQL 2005?

    Many thanks in advance.

  • You need a trigger or a trace. It's not something that SQL keeps track of itself.

    If you can give more detail on what you want, I can give you better suggestions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • For changes to the table structure, could you use Modify_Date in sys.object?

  • You can, but that's limited info. Doesn't tell you "what" changed. If it's soon enough, the default trace might help you.

  • Both sys.tables and sys.objects return the date of modifying table structure, not the date of modifying table data.

  • SQL ORACLE (1/27/2010)


    not the date of modifying table data.

    Hi,

    Record modified track!!

    Unless you follow the Gail approach or set the audit trial/table to the target table or the target table having the last modified date/timestamp columns, it’s not so easy to find out.

  • As others have already mentioned, sys.tables will show the time the structure of the table was last changed. And you will need a trigger to show when the data was last changed.

    So, here's an example:

    --make a couple of test tables

    if exists (select 1 from sys.tables where name = 'DataMod') drop table dbo.DataMod

    go

    if exists (select 1 from sys.tables where name = 'DataTest') drop table dbo.DataTest

    GO

    create table dbo.DataMod([schema] sysname, name sysname, modify_date datetime)

    go

    create table dbo.DataTest(TestCol1 int identity)

    go

    -- build a trigger to store off whenever the data is changed on the table

    CREATE TRIGGER [trg_data_test] ON dbo.DataTest for update, insert, delete AS

    insert into dbo.DataMod select 'dbo','DataTest', GetDate()

    GO

    -- show when the table was created and modified

    select name, create_date, modify_date from sys.tables where object_id = object_id('dbo.DataTest')

    GO

    -- add three records

    insert into dbo.DataTest DEFAULT VALUES

    GO 3

    -- add a new column to the table

    alter table dbo.DataTest add TestCol2 uniqueidentifier default newid()

    GO

    -- show that the table was modified

    select name, create_date, modify_date from sys.tables where object_id = object_id('dbo.DataTest')

    GO

    -- add three more records

    insert into dbo.DataTest DEFAULT VALUES

    GO 3

    -- show that the data was added, and that the table that stores when the table was last modified has been added

    select * from dbo.DataTest

    select * from dbo.DataMod

    GO

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • This would NOT catch updates to a non-indexed table and it only has info since the last SQL restart, but if THOSE conditions were not game-stoppers, would this be a viable solution to see data changes?

    select

    db_name(database_id) dbname,

    object_name(object_id,database_id) oname,

    MAX(CASE WHEN last_user_update < last_system_update THEN last_system_update ELSE last_user_update END) as LastUpdated

    from sys.dm_db_index_usage_stats

    group by database_id,object_id

    order by db_name(database_id),object_name(object_id,database_id)


    Cursors are useful if you don't know SQL

  • sys.dm_db_index_usage_stats does report on heaps, so it would include tables without indexes (index_id=0). But as mstjean mentions, the stats are only kept since the last server startup.

    jg

Viewing 9 posts - 1 through 8 (of 8 total)

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