How to check the last update date on a SQL Server table? 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.
How to check the last update date on a SQL Server table?
The 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.
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 a list of all tables and disk space in a database.