Changes to database tables

[luisdev]

I'm "analysing" an open source web application to understand how it
works so that I can contribute some code to it. When I want to see
what database records are added, removed and/or changed by a function
in the
application I just run a SELECT * FROM SOMETABLE before and after
running the function. Then I compare the two results of that SELECT
and I can see the changes to the data.

But is there a better way?

And is there a way to determine which tables have had records added,
removed and/or changed when I do something in the application?

Thanks.

[Jeroen Mostert]

Post by luisdev
I'm "analysing" an open source web application to understand how it
works so that I can contribute some code to it. When I want to see
what database records are added, removed and/or changed by a function
in the
application I just run a SELECT * FROM SOMETABLE before and after
running the function. Then I compare the two results of that SELECT
and I can see the changes to the data.
But is there a better way?

Use SQL Server Profiler to capture all statements the application executes.
You can do this without Profiler by adding triggers, but that's invasive and
potentially breaks the application. SQL Server 2008 adds Change Data Capture
(only available in Enterprise), but that's really intended for long-term
auditing of changes, not as a debugging aid. Just mentioning it for
completeness.

Post by luisdev
And is there a way to determine which tables have had records added,
removed and/or changed when I do something in the application?

Profiler has a replay function that allows you to re-execute captured
statements, so if you make a backup of the database just before you start
recording you can analyse the changes step by step.

[rpresser]

Since the OP mentioned working with an open source application, it seems possible to me that he is working with an Express edition of SQL Server. In which case SQL Profiler is not included with the product.

A free substitute is AnjLab Sql Profiler:
https://sites.google.com/site/sqlprofiler/

Or you can pay under $50 for SQL 2008 R2 Developer edition.

[Erland Sommarskog]

Post by luisdev
I'm "analysing" an open source web application to understand how it
works so that I can contribute some code to it. When I want to see
what database records are added, removed and/or changed by a function
in the
application I just run a SELECT * FROM SOMETABLE before and after
running the function. Then I compare the two results of that SELECT
and I can see the changes to the data.
But is there a better way?

You can have two copies of the database and use one as read-only
reference. Then you can run queries like

SELECT coalesce(a.keycol, b.keycol), a.col1, b.col1, ...
FROM refdatabase..tbl a
FULL JOIN testdatabase..tbl b ON a.keycol = b.keycol
WHERE a.keycol IS NULL
OR b.keycol IS NULL
OR a.col1 <> a.col1
...

To capture the difference. You have to be careful, though. It's easy to
wrong in a full join.

Post by luisdev
And is there a way to determine which tables have had records added,
removed and/or changed when I do something in the application?

For a full-fledge database comparison, there is SQL Data Compare from
Red Gate. This is not open source, but it costs money. There is a 14-
day eval period, though.