list most recent modified or updated tables in MS SQL 2008

Posted on 2011-05-12
Last Modified: 2012-06-21
How do i know which tables got modified or updated in SQL Server when any
update or modification is done from an End User using an Application Program Or What SQL
Statement should i use to list all the tables that have been modified on a given range of date
and time.
Question by:solarisinfosys
    LVL 33

    Expert Comment

    select as tableName, O.create_date, O.modify_date
    from sys.objects O
    where O.type = 'U'
    order by 3
    LVL 33

    Expert Comment

    Above I assume by "modified" you mean that the table definition has changed, not that the data in the table has changed.
    LVL 57

    Accepted Solution

    At data/records level, you can't get that information..
    But you can find the last time when your table structure is modified by running the query below:

    select name, modify_date
    from sys.tables
    where name = 'ur_table_name'
    and modify_date between x and y
    LVL 9

    Expert Comment

    You can simply query the sys.tables to get these information
    USE DatabaseName
    FROM sys.tables
    modify_date DESC

    Open in new window

    LVL 9

    Expert Comment

    If it is data changes you want you can

    1. Run a profiler and keep monitoring the data changes. This needs lot of disk space and there can be a perfomance hit on your server.

    2. You can read your transaction log using a tool such as Apex SQL Log (
    LVL 9

    Expert Comment

    Adding to the list there is another way you can get somewhat relavant details using Index statistics DMV. This works only for the tables and columns having indexes because these are index data update statastics.

    select,  max(u.last_user_seek) as LastSeek,  max(u.last_user_scan) as LastScan,  max(u.last_user_lookup) as LastLookup, max(u.last_user_update) as LastUpdate  from sys.dm_db_index_usage_stats u inner join sys.objects o on  o.object_id = u.object_id where o.type = 'U' and o.type_desc = 'USER_TABLE' group by

    FYI : LastSeek, LastScan and LastLookup all are reads  and LastUpdate   is updates.

    Author Closing Comment

    Thanks that worked perfectly

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now