0

I have 2 databases, they both share many of the same tables, I want to identify which tables do not exist one database but not the other. Is there any simple way of doing this?

Michael Brewster
  • 62
  • 3
  • Welcome to Stack Overflow! Please take the tour, have a look around, and read through [the help center](https://stackoverflow.com/help), in particular about [How to Ask a Question](https://stackoverflow.com/help/how-to-ask) and [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) in this site. Make an effort to solve the problem. If you run into a specific issue doing so, post a question with your attempt (including all the relevant code), saying what isn't working, and explaining your research so far. – Teocci Apr 26 '17 at 00:16

2 Answers2

0

Export the schema for the entire two databases in question and compare.

You can do that by using mysqldump

mysqldump -u root -p --no-data dbname > schema.sql
Dmitry Savy
  • 1,067
  • 8
  • 22
0

The simple answer is to get a list of tables from each database and compare them. This will work if you simply need to compare the list of tables, and not the actual structure of the tables.

From a SQL client environment or the mysql client:

use YOUR_DB_NAME;
show tables;

You will get a table with each row being the name of the table, as well as a helpful row count.

| sequence                |
+-------------------------+  
46 rows in set (0.01 sec)

The tables will already be in alphabetical order by name.

If you want to use some sort of diff tool, and need to get the list without the ascii box around it, you can start the mysql command tool up using the -s (silent) option:

mysql -u user -h host -p -s

Then you'll get a plain list which you can copy/paste or pipe output to a file.

gview
  • 14,876
  • 3
  • 46
  • 51