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?