CODEWITHSUNDEEP
mysql
Fero
Fero

Reputation: 13315

MySql- How to get a list of different tables from two different databases

I am having two mysql databases. One is NEW and other one is OLD.

I require the list of different tables between two databases. That is my old database is having the list of 155 tables and my new database is having the list of 165 tables.

How can I get the name of the TEN differnt tables ?

Is there any tool to do that or we can able to do via query ?

Any help will be thankful...

Thanks in advance..

Upvotes: 0

Views: 2795

Answers (3)

Andy Refuerzo
Andy Refuerzo

Reputation: 3332

You can do this by querying the INFORMATION_SCHEMA (a database which contains information of other databases in the server like table names, column names, primary key columns, primary key names, indexes, etc.) like this:

-- this gives the table names that are in the new table but not in the old table
select newTable.TABLE_NAME
from TABLES newTable
where newTable.TABLE_SCHEMA='NEW' and newTable.TABLE_NAME not in
(
    select oldTable.TABLE_NAME 
    from TABLES oldTable
    where oldTable.TABLE_SCHEMA='OLD'
)

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

Have you tried:

SHOW TABLES IN database;

or using information schema:

select table_schema, 
table_name from information_schema.tables
where table_name = ???;

EDIT AS PER OP'S COMMENT:

The INFORMATION_SCHEMA database is made up of temporary tables using the MEMORY storage engine.. All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.

For e.g. If your run following two commands you will see all the databases in your mysql metadata.

  • show databases;
  • use information_schema; show tables;

Here you are specifying a table_schema to get the table names.

SELECT table_name from 
information_schema.tables WHERE table_schema = 'mydb';

With a join: assuming one database name is db1, other db2

SELECT table_name from 
db1.tables x
inner join 
db2.tables
on x.table_name = y.table_name
;

Upvotes: 1

Nguyen Viet Anh
Nguyen Viet Anh

Reputation: 96

I think you should query on database information_schema. It's a table which contains all meta data of all database. Query something like:

    SELECT * FROM `TABLES` T1
LEFT JOIN `TABLES` T2
ON T1.`TABLE_NAME` = T2.`TABLE_NAME`
WHERE T1.`TABLE_SCHEMA`='xxx'
AND T2.`TABLE_SCHEMA`='yyy'
AND T1.TABLE_CATALOG IS NULL

Upvotes: 0

Related Questions