Check the last update date on a SQL Server table

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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*