mssql query to list tables where new rows has been inserted updated or deleted Edit
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)