SQLTeam.com | Weblogs | Forums

How to compare two databases tables meta data column definitions differences both databases are identical


#1

How to compare two databases tables meta data column definitions differences both databases are identical.

want to check if any differences in columns or db2 table has extra column.

select * from db1.table_orders
compare with
select * from db2.table_orders

Is it posssible, or checking the whole database tables.

Thanks a lot for the helpful info.


#2

you can try Red Gate's schema comparison tool.


#3

If you actually need to see the differences, run the given sample query.

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

Please go this to know more how to compare the two tables in a database. https://www.simple-talk.com/sql/t-sql-programming/on-comparing-tables-in-sql-server/


#4

Have a look at xSQL Schema Compare. You can use it to compare and then synchronize the schemas