Detailed Database Comparison Output

abjacs

Hi.

I am working on an application, and one piece of functionality is to output a detailed comparison between two databases, scripts folders, etc.

So far, my comparison output isn't very detailed and only shows output such as:
"OnlyIn1 Table dbo.Users"
"Different Role Superman"
etc.

Instead, I'd like very detailed output such as:
"Table <name of table here> has Index IX_UserKey"

Additionally, the SQL Compare GUI will show that one table in database A has changed compared to the same table in database B and will specifically show the SQL statement that is different.
How can I realize this type of output in my application?

Thanks for the help! :lol:

Michelle T

You can find the information such as "Table x has Index y" by looking through the individual objects held within each Difference object (or within the Database object for the database in question). For this specific example you would need to cast the IDatabaseObject to ViewTableSuperClass and then iterate through the Indexes property to get the table's indexes, and retrieve their names from the resulting Index objects.

The side by side comparison is mostly done in the user interface code. You can make a start at it with the API by using the ScriptObject method of a Work object on the object you want the SQL script for, but you'll have to implement your own comparison between the script of the two objects.

Hope that helps and is about the detail level you were looking for.

abjacs

Thanks for the reply.

So as I understand it I basically have two choices.
If I know that two tables are "different" i.e. DifferenceType.Different than I can:

1) Compare the regions in each table and somehow do a string comparison to output the exact difference(s) between the tables (I assume this is as close as I could get to the SQL Compare GUI)

or,
2) Take the database object for each database, iterate through and compare indexes, FK's, etc. between all the tables to find the exact difference(s).

Is this correct?
Thanks.

Michelle T

Yes, that's correct.

(You can extract the object you're looking at straight from the Differences collection without having to go via the Database object if you like, though - there are ObjectIn1 and ObjectIn2 fields in the Difference objects which contain the object itself. You'll have to cast it from IDatabaseObject to the correct database object class, but that shouldn't be too difficult as IDatabaseObject tells you the object type.)