MySQL: List databases with tables

Question

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

Answer 1

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

Answer 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;

Answer 3

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'