Check SQL Server table last update and access

How to check SQL Server table last update and access? Check the last access and update with a simple SQL query? Thanks to SQL Server RDBMS 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 SQL Server table last update and access?

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 we see 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 “Last User Update”.

In this post we discussed how to display the last access to an SQL Server table. You might want to know how to display a list of all tables and disk space in a database.

Be the first to comment

Leave a Reply