3

Is there a way to list all my databases with tables in a single command?

Expectation:

mysql> SHOW DATABASES, TABLES;

    DB1 table1
        table2
    DB2 table1
        table2
        table3
        table4
        table5
    DB3 table1
        table2
6

Use the information stored in INFORMATION_SCHEMA:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
ORDER BY table_schema, table_name;

table_schema is the database name.

To show also the databases without any tables:

SELECT s.schema_name, t.table_name 
FROM INFORMATION_SCHEMA.schemata AS s 
  LEFT JOIN INFORMATION_SCHEMA.tables AS t 
    ON t.table_schema = s.schema_name 
-- optional, to hide system databases and tables
-- WHERE s.schema_name NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY schema_name, table_name ;

Documentation links: INFORMATION_SCHEMA.tables, INFORMATION_SCHEMA.schemata

2

QUERY #1 (Separate Columns)

SET @db = '';
SELECT db,tb FROM
(SELECT @tb := IF(@db=table_schema,table_name,'') tb,
IF(@db=table_schema,'',table_schema) db,
(@db := table_schema) unused FROM information_schema.tables) A;

QUERY #2 (One Column)

SET @db = '';
SELECT CONCAT(db,'  ',tb) DBTB FROM
(SELECT @tb := IF(@db=table_schema,table_name,'') tb,
IF(@db=table_schema,'',table_schema) db,
(@db := table_schema) unused FROM information_schema.tables) A;

QUERY #3 (DB on First Table, Separate Columns)

SET @db = '';
SELECT db,tb FROM
(SELECT IF(@db=table_schema,'',table_schema) db,
table_schema tb,(@db := table_schema) unused FROM information_schema.tables) A;

QUERY #4 (DB on First Table, One Column)

SET @db = '';
SELECT CONCAT(RPAD(db,20,' '),tb) dbtb FROM
(SELECT IF(@db=table_schema,'',table_schema) db,
table_schema tb,(@db := table_schema) unused FROM information_schema.tables) A;
  • Thanks for your answer, in the Query #1 for any reason in each DB is missing the first table. How can I solve it? – tachomi Feb 26 '15 at 17:51
  • Both queries were designed to make DB disappear when displaying a table name and appear when changing databases. I did it this way to match the output you asked for in the question. – RolandoMySQLDBA Feb 26 '15 at 18:00
  • So there's no chance to make table 1 of each DB appear using this queries? – tachomi Feb 26 '15 at 18:04
  • Please post the exact output format in the question – RolandoMySQLDBA Feb 26 '15 at 18:24
  • Added QUERY #3 – RolandoMySQLDBA Feb 26 '15 at 18:34
-1

If you want to exclude MySQL internal databases,

SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' );

No need to add 'ORDER BY'

  • 1
    This answer is the same as @Phil one but only adding the condition and telling that the ̣̣̣̣̣̣̣̣̣̣̣ORDER BY clause is not necessary, I think this could be better as a comment to that answer. Anyway, thanks! – tachomi Feb 26 '15 at 18:18
  • @tachomi, I joined stack exchange today while it needs 50 reputation to add comment. I dont know if this was the reason for downvote – Koustuv Chatterjee Feb 26 '15 at 19:29
  • I didn't downvote you buddy :S – tachomi Feb 26 '15 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.