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 very useful for audit purpose or database troubleshooting, after data integration for example. The query show also the number of updates made and the last update time for the table or view.
How to check the last update date on a SQL Server table?
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’s 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.
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.