CODEWITHSUNDEEP
mysql
shantanuo
shantanuo

Reputation: 32316

Finding schema differences

I want to compare to DB Schema where the tables and columns are almost identical.

mysql> select a.TABLE_SCHEMA, a.TABLE_NAME FROM information_schema.tables as a left join information_schema.tables as b on a.TABLE_NAME = b.TABLE_NAME WHERE a.TABLE_SCHEMA = 'india' AND b.TABLE_SCHEMA = 'china' AND b.TABLE_NAME IS NULL;

I expected that the above query would return the tables those are present in India DB but are not there in China DB. But this does not seem to be working. Apart from Tables differences I do also need to find the columns those may be different in 2 DB's.

I do not want to use applications like Navicat. I need a query or UNIX command.

Upvotes: 1

Views: 119

Answers (1)

The Scrum Meister
The Scrum Meister

Reputation: 30111

You need to move the AND b.TABLE_SCHEMA = 'china' predicate from the WHERE to the JOIN:

select a.TABLE_SCHEMA, a.TABLE_NAME 
FROM information_schema.tables as a left join information_schema.tables as b
  ON b.TABLE_SCHEMA = 'china'
    AND a.TABLE_NAME = b.TABLE_NAME
WHERE a.TABLE_SCHEMA = 'india'  AND b.TABLE_NAME IS NULL

To find column information:

SELECT a.TABLE_NAME, a.COLUMN_NAME
FROM information_schema.columns a LEFT JOIN information_schema.columns b
  ON b.TABLE_SCHEMA = 'china'
    AND a.TABLE_NAME = b.TABLE_NAME
    AND a.ORDINAL_POSITION = b.ORDINAL_POSITION
    AND a.COLUMN_DEFAULT   = b.COLUMN_DEFAULT
    AND a.IS_NULLABLE      = b.IS_NULLABLE
    AND a.DATA_TYPE        = b.DATA_TYPE
    AND a.COLUMN_TYPE      = b.COLUMN_TYPE
.... Add other comparisons here depending on what you consider non identical.
WHERE a.TABLE_SCHEMA = 'india' AND b.COLUMN_NAME IS NULL

Upvotes: 1

Related Questions