Finding out which tables are different in two versions of a database

Question

I have 2 versions of a database (say db_dev and db_beta). I've made some changes in the db_dev database - added some tables, and changed a few columns in some existing tables. I need to find out the list of table names in which changes have been made.

I can easily find out the tables I've added by running the following query on the information_schema database:

SELECT table_name
FROM   tables
WHERE  table_schema = 'db_dev'
       AND table_name NOT IN (SELECT table_name
                              FROM   tables
                              WHERE  table_schema = 'db_beta');

How do I get the table_names whose column_names do not match in the two database versions?

Are you working with visual studio? if yes, then tell me. – Ajay2707 Feb 25 '15 at 09:36 — Ajay2707, Feb 25 '15 at 09:36

score 2 · Answer 1 · answered Feb 24 '15 at 17:10

2

There are many ready made tools available which can give you changed schema by comparing two databases. Here are some tools which can serve your purpose :

Red-Gate's MySQL Schema & Data Compare

Maatkit

MySQL Diff

SQL EDT

Red-Gate's MySQL Compare is best tool for this purpose. Its paid though but they provide 14 days free trial version if you want to do something temporary.

answered Feb 24 '15 at 17:10

Hiren Dhaduk

2,760
2
19
21

"Maatkit" is old; it has been taken over and improved by Percona as the Percona Tool Kit. – Rick James Feb 25 '15 at 07:23

score 1 · Answer 2 · edited May 23 '17 at 10:26

1

The following solution does not use an sql query like you tried and does not give you a real list of tables, but it shows you all the changes in both databases.

You can do an sql dump of both database structures :

mysqldump -u root -p --no-data dbname > schema.sql

Then you can compare both files, e.g. using the diff linux tool.

edited May 23 '17 at 10:26

Community

1
1

answered Feb 17 '15 at 07:43

Christophe Weis

2,518
4
28
32

Adam · Accepted Answer · 2015-02-25T15:42:11.603

Using information_schema, here is how it works.

First, you know that the information_schema.COLUMNS table contains the columns definition. If one column has been changed, or a table does not exist, it will reflect in the information_schema.COLUMNS table.

Difficult part is that you have to compare all columns of your COLUMNS table. So, you have to select TABLE_CATALOG,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT, and so on (which is subject to evolution depending on your MySQL version). The column list is the result of the following query:

  SELECT GROUP_CONCAT(column_name)
  FROM information_schema.COLUMNS
  WHERE table_schema="information_schema"
       AND table_name="COLUMNS" AND column_name!='TABLE_SCHEMA';

After that, we just have to SELECT TABLE_NAME, <column_list> and search for columns which appear once (column inexistent in other table), or where columns have two different definitions (columns altered). So we will have two different count in the resulting query to consider the two cases.

We will so use a prepared statement to retrieve the list of column we want, and grouping the result.

The resulting query does all the process for you:

 SELECT CONCAT(
      "SELECT DISTINCT TABLE_NAME
           FROM information_schema.COLUMNS
           WHERE TABLE_SCHEMA IN('db_dev', 'db_beta')
           GROUP BY table_name, COLUMN_NAME
           HAVING count(*)=1 OR 
           COUNT(DISTINCT CONCAT_WS(',', NULL, ",
               GROUP_CONCAT(column_name)
           ,"))=2;")  
 FROM information_schema.COLUMNS
 WHERE table_schema="information_schema"
    AND table_name="COLUMNS" AND column_name!='TABLE_SCHEMA'
 INTO @sql;

 PREPARE stmt FROM @sql;
 EXECUTE @sql;

Finding out which tables are different in two versions of a database

3 Answers3