How to figure out how many tables are affected in database after inserting a record?

Question

One third party app is storing data in a huge database (SQL Server 2000/2005). This database has more than 80 tables. How would I come to know that how many tables are affected when application stores a new record in database? Is there something available I can retrieve the list of tables affected?

Answer 1

You might be able to tell by running a trace in SQL Profiler on the database - the SQL:StmtCompleted event is probably the one to monitor - i.e. if the application does a series of inserts into multiple tables, you should see them go through in Profiler.

Answer 2

You can use SQL Profiler to trace SQL queries. So you will see sequence of calls caused by one button click in your application.

Also use can use metadata or SQL tools to get list of triggers which could make a lot of actions on simple insert.