Check SQL Server table latest logs like Last Updated, Last Access

There are situations when we need to check the last updated entry in a database table. So how do we check that last updated entry in SQL Server? SQL Server RDMS is so advanced that system tables allow us to provide that information. A query on system tables allows us to display the last DB changes performed. We can also check other information like last scans, last updates, etc

Here is the query to get last updated/accessed logs 

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()

This feature is very useful for audit purposes 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. You can see below screenshot to check how above mentioned query actually fetch the records.

Another easy way to check last time the table was updated in terms of its structure has changed (new column added, column changed, etc.) - use this query:

SELECT name, [modify_date] FROM sys.tables

This is how we get the last updated/scanned/accessed logs of any table. Most of the DBA are known to these SQL queries you might want to know more about SQL Server DBA Roles and Responsibilities. 

Here are the other important topics :

Use of Case Expression in SQL Server with Example

Using SQL Server Cursors - General concepts
Temporary Tables in SQL Server - Local & Global