s

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

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

author
Murugan Andezuthu Dharmaratnam | calendar 05 February 2021 | 1883

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)