In terms of clarification, when the last table had a modification, there is a hidden way that can work to access this information, but it will not tell you which row was changed only when. SQL Server maintains index usage statistics and records the last index search / scan / search and update. It also breaks it down into a user / system.
Filtering is only for user tables, any insert / update / delete will cause an update to appear in the index, and the DMV will update this new information.
select o.name, max(u.last_user_seek) as LastSeek, max(u.last_user_scan) as LastScan, max(u.last_user_lookup) as LastLookup, max(u.last_user_update) as LastUpdate from sys.dm_db_index_usage_stats u inner join sys.objects o on o.object_id = u.object_id where o.type = 'U' and o.type_desc = 'USER_TABLE' group by o.name
However, it is not perfect, the heap does not have an index to start with - and I never considered using it for production code as a tracking mechanism, only as a forensic tool to check for obvious changes.
If you need to properly track changes at the row level, you will have to either create one or look at the specific SQL 2008 data modification function.
source share