List all databases in MySQL with SHOW DATABASES statement

This article describes two ways to list all databases in MySQL, SHOW DATABASES and information_schema.schemata.

This article describes two ways to list all databases in MySQL: SHOW DATABASES and information_schema.schemata table.

As a database administrator or maintainer, knowing how to show databases are there in the current MySQL database server is a must-have skill.

MySQL stores all database information in the information_schema.schemata table, which you can query directly. Or you can use SHOW DATABASES command.

Show Database command

To list all databases in the MySQL server, you can use the following command:

SHOW DATABASES;

or

SHOW SCHEMAS;

SHOW SCHEMAS is a synonym for SHOW DATABASES, they have the same return result.

If the user running this command is not superuser, only a list of databases that the user has privileges is returned.

You can also use LIKE clause to filter the results as follows:

SHOW DATABASES LIKE pattern;

Example: List all databases

Let’s run the following command using the root user to get all the databases:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| adam_test          |
| bags               |
| cad                |
| dc                 |
| information_schema |
| mysql              |
| native_test        |
| performance_schema |
| sakila             |
| sqlizdb            |
| sys                |
| testdb             |
+--------------------+

Here, the root user is superuser, so he can view all databases.

Let’s run the following statement using sqliz user:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| sqlizdb            |
+--------------------+

Here, the sqliz user is a normal user, so he can only view the database with permissions.

Example: Filtering databases

The following statement uses LIKE to get all databases starting with s:

SHOW DATABASES LIKE 's%';

+---------------+
| Database (s%) |
+---------------+
| sakila        |
| sqlizdb       |
| sys           |
+---------------+

Find databases from the schemata table

MySQL stores all database information in the information_schema.schemata table, you can directly query this table to get all databases.

In fact, the SHOW DATABASES command is the same as the following query statement:

SELECT schema_name FROM information_schema.schemata;

Let’s run the above statement with the root user. Here’s what the statement returns:

+--------------------+
| SCHEMA_NAME        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| cad                |
| bags               |
| adam_test          |
| dc                 |
| sakila             |
| testdb             |
| native_test        |
| sqlizdb            |
+--------------------+

If you want to get more information, you can use the following statement:

SELECT * FROM information_schema.schemata;

+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def          | mysql              | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | information_schema | utf8                       | utf8_general_ci        |     NULL | NO                 |
| def          | performance_schema | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | sys                | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | cad                | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | bags               | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | adam_test          | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | dc                 | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | sakila             | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | testdb             | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | native_test        | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | sqlizdb            | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+

Here, more information is returned, such as character set, collation, etc.

Conclusion

In this article, you learned two ways to get all databases information from a MySQL database server:

  1. Using SHOW DATABASES command
  2. The information_schema.schemata table . This table contains more information that SHOW DATABASES.