Get list of all tables and columns "touched" by application I need to find out where in the database changes are made, originating from an application. In other words: I'm not interested in the actual value changes, I'm interested in where these changes are taking place. Can this be done and if so, how?
Context
I'm tasked with managing a gigantic application that interacts with a very large database. Even though I have the source code available it is proving quite difficult to track which changes in the database the different parts of the application cause. More specifically which tables and columns get &quot;touched&quot; by the actions invoked from the application.
I have a test system set up (a clone of production) with it's own database. I am the only user on the test system so I can safely say that triggered changes are caused by my own actions.
I've been reading up on CDC, but as far as I can tell if you don't know which columns are being touched you have to query ALL the columns by name. This is problematic considering the database holds hundreds of tables with hundreds of columns.
Is there any other way to get a list of changes in the database per action?
It sounds like, for this particular scenario, you should run a trace in SQL Profiler. You can open it from the Tools menu in SSMS or find it in the Start > Applications > SQL Server 2008 R2 > Performance Tools folder.

Create a new trace and connect it to your database. Use the "blank" template and in the Events Selection tab just select Stored Procedures > RPC:Completed and TSQL > SQL:BatchCompleted.

Pause (but not stop) your trace until you're ready to click something, then start the trace back up and see exactly what queries the application produces. If you're still getting a lot of noise in your traces, you can use the column filters and the CientProcessID or SPID fields will probably help you nail it down.