CODEWITHSUNDEEP
mysqlsqlmapping
user8116883
user8116883

Reputation:

Map table names of a database with values in another table

I have some table names in test database in MySQL. I have a table called mapping in database renamed that has information of tables that have been renamed.

Now I want to have a list of the tables that are in test database and their renamed name in the mapping table.

Tables in test

123_abc
124_abc
235_test

Information in mapping table

table       renamed

123_abc     123_abc_test
235_test    235_testing
abc_test    abc_test1

Now I want a list that contains table names that are in test database and their renamed names in the mapping table in renamed database

The expected result is

123_abc     123_abc_test
235_test    235_testing

How can I achieve that?

Upvotes: 0

Views: 509

Answers (2)

S.Sabari Giri Nathan
S.Sabari Giri Nathan

Reputation: 61

You can issue a MySQL statement like,

select * from renamed.mapping inner join information_schema.tables on renamed.mapping.table_name = information_schema.tables.TABLE_NAME where information_schema.tables.TABLE_SCHEMA='test'

Upvotes: 1

Osuwariboy
Osuwariboy

Reputation: 1375

You could use the information_schema database that's part of MySQL and which contains all of MySQL's meta-information (including table names) like this:

select t1.TABLE_NAME, t2.table_name 
from information_schema.`TABLES` t1 
where table_schema = 'test' 
inner join renamed.mapping t2 on 
    t1.table_name = t2.table_name

Granted, with an inner join, you'll only get the tables that are actually renamed. If you want all the tables in your test schema, do a left join instead.

Upvotes: 0

Related Questions