mssql query to list tables where new rows has been inserted updated or deleted Edit

Murugan Andezuthu Dharmaratnam | 05 February 2021 | 164

Here is the query to check which all tables had a recent insert update or delete in the last n minutes. This is one of the queries I frequently use while I am testing an application and want to find out the list of tables where data has been modified.

Solution / SQL Query

declare @seconds int = 600
select * from (select sys.databases.[name] as dbname, sys.objects.name as tablename, DATEDIFF(SECOND, last_user_scan, GETDATE()) as scan,DATEDIFF(SECOND, last_user_update, GETDATE()) as [last_insert_update_delete],DATEDIFF(SECOND, last_user_lookup, GETDATE()) as [lookup] 
from sys.dm_db_index_usage_stats
inner join sys.databases on sys.databases.database_id = sys.dm_db_index_usage_stats.database_id
inner join sys.objects on sys.objects.object_id = sys.dm_db_index_usage_stats.object_id
where last_user_scan IS NOT NULL or last_user_update is not NULL or last_user_lookup IS NOT NULL) td
where ([last_insert_update_delete] < @seconds)