Listing all databases is a common administrative task in MySQL. Here are a few ways to list all the databases, and how to further view the tables in each database.

Use the SHOW DATABASES command. SHOW DATABASES is the most common command in MySQL to list all databases. It returns a result set containing all the database names in the current MySQL instance.

SHOW DATABASES;

Example output:

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| my_database        |

| another_database   |

+--------------------+

If you need to sort the results, you can use the ORDER BY clause. For example, sort by database name in ascending order:

SHOW DATABASES ORDER BY Database ASC;

Use the SHOW SCHEMAS command. SHOW SCHEMAS are synonyms of SHOW DATABASES and have the same function.

SHOW SCHEMAS;

Use the LIKE clause to filter the database. If you only want to find a database for a specific schema, you can use the LIKE clause. For example, find all databases that start with my_ :

SHOW DATABASES LIKE 'my_%';

View the tables in each database. To view all the TABLES in a database, select the database and then use the SHOW TABLES command.

USE my_database; SHOW TABLES;

Example output:

+-------------------+

| Tables_in_my_database |

+-------------------+

| table1            |

| table2            |

+-------------------+

Use the system table INFORMATION_SCHEMA. To list all databases and their tables at the same time, query the system tables INFORMATION_SCHEMA.SCHEMATA and INFORMATION_SCHEMA.TABLES.

SELECT 

SCHEMA_NAME AS 'Database',

TABLE_NAME AS 'Table'FROM 

INFORMATION_SCHEMA.SCHEMATAJOIN 

INFORMATION_SCHEMA.TABLES ON 

SCHEMA_NAME = TABLE_SCHEMA;

Automation with command line scripts. In a practical application, you can automate the listing of all databases and their tables through scripts. Here is an example of a simple MySQL script:

SELECT 

GROUP_CONCAT(CONCAT('SHOW TABLES FROM ', SCHEMA_NAME) SEPARATOR '; ') INTO 

@sql FROM 

INFORMATION_SCHEMA.SCHEMATA;

PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Listing all the databases and their tables in MySQL is a basic administrative task. You can easily obtain database and table information by using SHOW DATABASES, SHOW TABLES, and system table INFORMATION_SCHEMA. These methods are not only suitable for day-to-day management, but also enable more efficient management through script automation.