patternsqlModerate

MySQL: List databases with tables

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tablesdatabaseswithmysqllist

Problem

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

Solution

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

Code Snippets

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

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 ;

Context

StackExchange Database Administrators Q#93919, answer score: 11

Revisions (0)

No revisions yet.