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 can add triggers on tables that get fired on update - you could use this to update a log table that would report what was being updated.

see more here: http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/

Answer 2

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 3

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.

Answer 4

Profiler is the way to go, as others have said especially with an unfamilar third party database.

I would also spend some time creating diagrams so you can see the foreign key relationships and understand how the database is put together. I usaully know my database structure so well, I can tell from the fields being inserted what tables they affect and I know what triggers are on my tables and what they affect. There is no substitute for taking the time to understand the database you support.