Comparing tables of different databases after migration

Recently I have migrated my project from SQLServer 2000 to MySQL 5.2 using MySQL migration toolkit although at the end of migration it shows the message that migration successful but i am still not sure wheth开发者_StackOverflow中文版er the data migrated successfully or not because while migrating in the back end this migration toolkit do something like it changed the bit Datatype to tiny int, this one i have noticed but i am not sure what else it does as i am having some sensitive data so i want to be sure from my side whether migration is actually successful or not in the same time i cannot do this manually due to huge amount of data are there any ways so that i can compare tables from both sqlserver 2000 database and Mysql database and generate a report. By comparing i mean comparing table as a whole including data, datatype, everything.

I doubt there is a tool for this, so you will most likely need to write your own and it is going to take some time.

The first thing I would do is write an application that connects to both databases, grab the table definitions into memory, and output them to a file in order of table name.

Then use a file comparision tool like WinMerge to check for differences. If there are differences like the one you mentioned above, make the necessary changes in MySQL and map the data if necessary.

After that I would modify the same application to cycle through every table in the database, bring back all the data into some type of set-based object that supports a UNION operation. UNION the old table with the new table and check to see if the number of rows in the result equals the number of rows in the source database. If so, all the values are the same, other wise mark it for further inspection. This will most likely take care of most of your tables and you can do a manual inspection of the ones that fail this test.

All the other objects will be variation on a theme (they are simpler objects, just text), but I would spend the majority of my time on verifying the data.

Hope this helps, its going to be a time consuming process for you any way you look at it.