MySQL - List all Databases

Listing of databases, respective tables in the database, or retrieving user accounts and privileges within MySQL database servers are few of the most common tasks you’ll come across.

In this article, you will see how to retrieve the list of databases with MySQL.

List MySQL SHOW DATABASES

To list all databases on a MySQL server, use the SHOW DATABASES command as follows:

SHOW DATABASES;

Here we’re executing this command on via MySQL CLI. You can use this same command on phpMyAdmin.

To list all databases in the local MySQL database server, the first login to the database server is as follows:

> mysql -u root -p
> Enter password: ****

Now let us execute the actual command to retrieve all databases as shown below.

> mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| practice           |
+--------------------+
5 rows in set (0.00 sec)

The MySQL also provides an alternative keyword to retrieve the same result with SCHEMAS keyword with command SHOW SCHEMAS;.

> mysql> SHOW SCHEMAS;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| practice           |
+--------------------+
5 rows in set (0.00 sec)

The MySQL also allow us to filter databases or schemas with WHERE clause. We can query the schemas that match a specified pattern, with the use of LIKE clause also as shown below:

Filtering Databases with LIKE clause

SHOW DATABASES LIKE search_pattern;
-- OR
SHOW SCHEMAS LIKE search_pattern;

Query 1:

Let us search all databases that end with the ‘schema’.

Command

SHOW DATABASES LIKE '%schema';

Output

+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

Query 2:

Let us search all databases that contain the '%i%' character.

Command

SHOW DATABASES LIKE '%i%';

Output

+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| practice           |
+--------------------+
2 rows in set (0.00 sec)

Alternate way to retrieve database list

The MySQL also provides an alternative approach for listing or filtering databases by querying the schemata table from the information_schema database that holds information about all databases.

Query 3:

Let us list all databases that end with the ‘schema’.

Command

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%schema';

This query returns the same result as the SHOW DATABASES; or SHOW SCHEMAS command.

Output

+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

In this article, we’ve gone through retrieval & filtering of databases or schemas in the MySQL server using the different commands or querying from the schemata table within information_schema database.

Note: If the MySQL database server is started with --skip-show-database option, then the SHOW DATABASES command will not work if you do not have the SHOW DATABASES privileges.

Hope you like this! If this article was helpful, share it.

Keep helping and happy 😄 coding