MySQL SHOW DATABASES: List All Databases in MySQL

 MySQL SHOW DATABASES: List All Databases in MySQL

Summary: in this tutorial, you will learn how to use the MySQL SHOW DATABASES command to list all databases in a MySQL database server.

Using the MySQL SHOW DATABASES

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

SHOW DATABASES;

For example, to list all database in the local MySQL database server, first log in to the database server as follows:

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

And then use the SHOW DATABASES command:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

The SHOW SCHEMAS the command is a synonym for SHOW DATABASES, therefore the following command returns the same result as the one above:

SHOW SCHEMAS;

If you want to query the database that matches a specific pattern, you use the LIKE clause as follows:

SHOW DATABASES LIKE pattern;

For example, the following statement returns a database that ends with the string 'schema';

SHOW DATABASES LIKE '%schema';
+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

It is important to note that if the MySQL database server started with --skip-show-database, you cannot use the SHOW DATABASES statement unless you have the SHOW DATABASES privilege.

Querying database data from information_schema

If the condition in the LIKE a clause is not sufficient, you can query the database information directly from the schemata table in the information_schema database.

For example, the following query returns the same result as the SHOW DATABASES command.

SELECT schema_name 
FROM information_schema.schemata;

The following SELECT the statement returns databases whose names end with 'schema' or 's'.

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

It returns the following result set:

+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| performance_schema |
| sys                |
| classicmodels      |
+--------------------+
4 rows in set (0.00 sec)

In this tutorial, you have learned how to show all databases in the MySQL server using the SHOW DATABASES command or querying from the schemata table in the information_schema database