Query to study Transactional Changes in MSSQL Database

by Shozab Hasan | Feb 17, 2012 | Software Development

When you have a Management System or any other type of Database application without technical documentation, you may be having a difficulty to find out and making the transactional flow of a procedure. There is a way to make this work out easy. You can use following query to run after a transaction. This query will give you the name of tables that will be affected with respect to time. Then you can easily judge the flow of a transaction. Always record the time at every trial.

 select
 t.name
 ,last_user_update
 ,user_seeks
 ,user_scans
 ,user_lookups
 ,user_updates
 ,last_user_seek
 ,last_user_scan
 ,last_user_lookup
 from
 sys.dm_db_index_usage_stats i JOIN
 sys.tables t ON (t.object_id = i.object_id)
 where
 database_id = db_id()
 ORDER BY last_user_update DESC