Last time table was updated in SQL Server

How to check the last date a table was updated on a SQL Server database? Check the last access and update with a simple SQL query? Thanks to the SQL Server relational database management system tables, a query allows you to display the last changes done. We can apply it to any SQL Server table or view, like last scans, or last updates for example.

This feature is especially useful for audit purpose or database troubleshooting, after data integration for example. The query shows also the number of updates made and the last update time for the table.

Get the last date and time a table was updated in SQL Server

Indeed, the goal here is to know when a specific table has been updated. For exemple when the SQL framework in place do not update metadata like insert date or update.

Please note that the metadata is something highly recommended especially with sensitive data and large amount of data.

The SQL query uses the table sys.tables and the sys.dm_db_index_usage_stats system tables.

SELECT	
    object_name(STAT.object_id) AS ObjectName, 
    USER_SEEKS,
    USER_SCANS,
    USER_LOOKUPS,
    USER_UPDATES,
    LAST_USER_SEEK,
    LAST_USER_SCAN,
    LAST_USER_LOOKUP,
    LAST_USER_UPDATE
FROM	SYS.DM_DB_INDEX_USAGE_STATS STAT JOIN
        SYS.TABLES TAB ON (TAB.OBJECT_ID = STAT.OBJECT_ID)
WHERE 	DATABASE_ID = DB_ID();

In this example, let us consider only the sales table, and the number of access made is one.

I.e., for reading the data, the information is in the column “Last User Scan“. We also see one update in the column named “Last User Update“.

To achieve this result, very useful in some cases, the example uses the SYS.DM_DB_INDEX_USAGE_STATS system table.

**Get the last date a table was updated on SQL Server**

As per the screenshot, it also displays the seeks, the scans and lookups. Please note that these tables contains many other useful statistics on table.

This article explains how to display the last access and update date to an a given SQL Server table. This SQL code helps for compliance and troubleshooting.

Check this other article to learn how to display manage SQL Server tables with T-SQL scripts.