Mysql - find a table in all database

Question

Is there any command that I can locate a certain table from all databases? Since I forgot where the table is. Thanks.

score 45 · Accepted Answer · edited Aug 06 '18 at 12:52

45

SHOW TABLES

SELECT table_name,
       table_schema AS dbname
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_name='searched table name'

edited Aug 06 '18 at 12:52

answered Sep 21 '10 at 01:49

OMG Ponies

But in this way, I still don't know which database that table belongs to. – Stan Sep 21 '10 at 01:55
@Stan: I updated the INFORMATION_SCHEMA query to exist the database the table exists in. – OMG Ponies Sep 21 '10 at 01:59
5

WHERE table_name = 'my_table_name' – Geoffrey Hale Dec 29 '15 at 21:16
1

So if she knew what she wants then she could use the full query as :- SELECT table_name, table_schema AS dbname FROM INFORMATION_SCHEMA.TABLES where table_name='her_table_name' – John Doe Dec 25 '16 at 19:13

1 Answers1