Find missing table by comparing two different databases

Question

I would like to find a missing table in a database.

I have two databases:

1. database_1 (It contains 120 tables)
2. database_1_backup (It contains 121 tables)

What I have done so far:

I found the list of tables in two databases by using the following sql query:

show tables;

Using the tables list, I made two text files, then compared two textfiles. That is how I found the missing table (That was a time-consuming task, so).

Is there any query available to find missing table by comparing two databases?

Answer 1

You can use aggregation to find tables missing in either database:

SELECT TABLE_NAME,
       (CASE WHEN SUM(TABLE_SCHEMA = 'dbname1') = 0
             THEN 'Missing in dbname1'
             ELSE 'Missing in dbname2'
        END) as which
FROM information_schema.TABLES
WHERE TABLE_SCHEMA IN ('dbname1', 'dbname2')
GROUP BY TABLE_NAME
HAVING COUNT(*) = 1
ORDER BY TABLE_NAME;

Answer 2

Querying information_schema.TABLES should give you the answer you are looking for, try this to find to missing table:

SELECT table_name     
FROM information_schema.TABLES 
WHERE table_schema = 'database_1_backup'
AND table_name NOT IN (
  SELECT table_name 
  FROM information_schema.TABLES
  WHERE table_schema = 'database_1'
);