469,579 Members | 1,177 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Compare data in Tables

Leo
I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema, run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively); I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

Leo
Jul 23 '05 #1
3 1604
Ray
If its changes you are looking for try running SQL Profiler against it.
Filter for where writes > 0. Another solution would to write a script to
doing the all tables comparison. Something like

Create a table with tablename, checksumbefore, checksumafter, rowsbefore,
rowsafter, numberofrowsdiff
Write a cursor of all user tables
For each table
Get count of rows with select count(*)
calc the CHECKSUM of each row and write to individual temp tables
select count(*) from checksumafter where checksum not in checksumbefore
insert/update the table

By the end of the script you should have indentified which tables change and
by how much.
"Leo" <le*****@optushome.com.au> wrote in message
news:Xn*********************************@211.29.13 3.50...
I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema,
run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively); I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

Leo

Jul 23 '05 #2
There is a software tool that can do this for you called DB Ghost
(www.dbghost.com). Its very fast at comparing data and can be run from
the command line for a fully automated process. A single command will
do any number of tables that you desire.

It's also the cornerstone of a full change management solution for SQL
Server databases i.e. it can build, compare and synchronize the schema
AND data directly from drop/create scripts held in a source control
system.

I highly recommend you check it out.

Jul 23 '05 #3
"Malcolm" <ma***********@innovartis.co.uk> wrote in
news:11**********************@l41g2000cwc.googlegr oups.com:
DB Ghost


DB Ghost did exactly what I needed.

Thanks for your advice

Leo
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Stephen | last post: by
8 posts views Thread by Vincent | last post: by
1 post views Thread by Mark | last post: by
reply views Thread by Shaw | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.