Loop through some databases searching for a table

Question

I want to loop through some databases (the count of the databases are undefined) searching for a table and than I want to make SELECT and UPDATE queries in that DB where I found that table.

http://stackoverflow.com/questions/8334493/get-table-names-using-select-statement-in-mysql — Al Amin Chayan, May 22 '17 at 06:45

score 1 · Accepted Answer · answered May 22 '17 at 06:57

You can do a select against INFORMATION_SCHEMA.TABLES to locate the table by name:

SELECT 
    `TABLE_SCHEMA`,
    `TABLE_NAME`,
    `TABLE_ROWS`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_NAME` like '%_assets'

Unless the desired table names might vary (different prefix, capitalization, etc.), use = 'name' instead of like '%_assets'

score 0 · Answer 2 · answered May 22 '17 at 06:41

0

There are two solutions to your problem:

1). MySQL based using Federated Engine (as mentioned by you). You can go through this Blog post on Federated Engine

2). (As you are using PHP) Fetching data from one server, then either looping through the data and firing relevant queries to another server, or firing single aggregated query to another server based on the data fetched from first server.

Hope it will help you...

answered May 22 '17 at 06:41

Bilal Ahmed

4,005
3
22
42

You could use a select against INFORMATION_SCHEMA to find which database contains the table. See https://dev.mysql.com/doc/refman/5.7/en/tables-table.html for more info. – Sloan Thrasher May 22 '17 at 06:42

score 0 · Answer 3 · answered May 22 '17 at 06:45

To list the various tables in a database you can query the database schema for the info you want.

select `table_name` as 'name' 
            from `information_schema`.`tables` 
            where `table_type`='base table' and `table_schema`=database();

The same approach can be used to find other items in the database such as events,triggers,stored procedures etc etc

Loop through some databases searching for a table

3 Answers3