Query to study Transactional Changes in MSSQL Database
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