Compare structures of two databases?

Question

I wanted to ask whether it is possible to compare the complete database structure of two huge databases. We have two databases, the one is a development database, the other a production database. I've sometimes forgotten to make changes in to the production database, before we released some parts of our code, which results that the production database doesn't have the same structure, so if we release something we got some errors. Is there a way to compare the two, or synchronize?

Answer 1

For MySQL database you can compare view and tables (column name and column type) using this query:

SET @firstDatabaseName = '[first database name]'; SET @secondDatabaseName = '[second database name]';  SELECT * FROM     (SELECT       CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType     FROM information_schema.columns cl,  information_schema.TABLES ss     WHERE       cl.TABLE_NAME = ss.TABLE_NAME AND       cl.TABLE_SCHEMA = @firstDatabaseName AND       ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')     ORDER BY       cl.table_name ) AS t1 LEFT JOIN                        (SELECT       CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType     FROM information_schema.columns cl,  information_schema.TABLES ss     WHERE       cl.TABLE_NAME = ss.TABLE_NAME AND       cl.TABLE_SCHEMA = @secondDatabaseName AND       ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')     ORDER BY       cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowType WHERE    t2.tableRowType IS NULL         UNION  SELECT * FROM     (SELECT       CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType     FROM information_schema.columns cl,  information_schema.TABLES ss     WHERE       cl.TABLE_NAME = ss.TABLE_NAME AND       cl.TABLE_SCHEMA = @firstDatabaseName AND       ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')     ORDER BY       cl.table_name ) AS t1 RIGHT JOIN                        (SELECT       CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType     FROM information_schema.columns cl,  information_schema.TABLES ss     WHERE       cl.TABLE_NAME = ss.TABLE_NAME AND       cl.TABLE_SCHEMA = @secondDatabaseName AND       ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')     ORDER BY       cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowType WHERE    t1.tableRowType IS NULL; 

If you prefer using tool with UI you can also use this script https://github.com/dlevsha/compalex which can compare tables, views, keys etc.

Compalex is a lightweight script to compare two database schemas. It supports MySQL, MS SQL Server and PostgreSQL.

Screenshot (compare tables)