We have a database with hundreds of tables.
Is there some kind of meta data source in SQL Server that I can programatically query to get the name of the last changed table and row?
Or do we need to implement this ourselves with fields in each table called LastChangedDateTime, etc.?
In terms of finding out when a table last had a modification, there is a sneaky way that can work to access this information, but it will not tell you which row was altered, just when. SQL Server maintains index usage statistics, and records the last seek / scan / lookup and update on an index. It also splits this by user / system.
Filtering that to just the user tables, any insert / update / deletion will cause an update to occur on the index, and the DMV will update with 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
It is not ideal however, a heap has no index for a start - and I have never considered using it for production code as a tracking mechanism, only as a forensic tool to check obvious alterations.
If you want proper row level alteration tracking you will either have to build that in, or look at the SQL 2008 specific Change Data Capture feature.