How To Find The Last Time A Table Was Updated In SQL Server

Faruk Erdem

3 years ago

In this article, we will answer the question How To Find The Last Time A Table Was Updated In SQL Server.

We can use indexes to learn from which tables in a database data is drawn.

By finding when “seek”, when “scan” is done from the indexes, we can find when those tables were used last, but if “table scan” is done on the table, the following command will not work.

USE Database_adi
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
GROUP BY ius.[database_id], ius.[object_id];

USE Database_adi

SELECT DB_NAME(ius.[database_id]) AS [Database],

OBJECT_NAME(ius.[object_id]) AS [TableName],

MAX(ius.[last_user_lookup]) AS [last_user_lookup],

MAX(ius.[last_user_scan]) AS [last_user_scan],

MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius

WHERE ius.[database_id] = DB_ID()

GROUP BY ius.[database_id], ius.[object_id];