compare data while migrating data

Hi All,

In my current project we are combining data from one DB to another Db with almost similar schema. The differences are sometimes in datatype length for a column ex: varchar(30) in one DB and varchar(50) in another. sometimes extra columns are available in tables in either DB. we have packages written to bring data from old one to new.

I would like to check the below:

1. Compare two tables in two DBs to check if their columnnames are same and if same ,whether the data type is same or not.
2. Also compare if data is properly copied from one table to another. assume in student table if i give sample student id i need to compare data from both tables and display 'data correct' or 'data incorrect'
3. if extracolumns are available for given table in either DB they should be displayed along with data type.

right now i am doing this manually. But i thought we can do this using meta data tables available in SQL Server. The table names are same in both DBs.
Can anyone help me with this with pointers in this direction?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

As you said, all the structural information are in meta data tables. I think this is the starting point you need:

You can made a Transact-SQL process which mades this, just consulting what you need on these system views and act according to them.

Hope this helps. Regards.
Jim HornMicrosoft SQL Server Data DudeCommented:
Regarding your points 1 and 3, a vastly better way to identify ALL differences in schema between two databases is...

Open Visual Studio / SQL Server Data Tools (SSDT).  If you don't have this install it.
SQL menu item >Schema Compare > New Schema Comparison...
Set connections in the 'Source' left side and 'Target' right side to your two db's.
Hit the Compare button, and follow the prompts

You'll get a list of every schema difference between the two db's.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Jyozeal, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.