MySQL SHOW DATABASES

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

Introduction to the MySQL SHOW DATABASES

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

SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)

First, open the command prompt on windows or terminal on macOS or Linux.

Second, connect to the MySQL server:

mysql -u root -p

It’ll prompt you to enter a password for the root account. After you enter the correct password, you’ll be connected to MySQL server.

Third, issue the SHOW DATABASES command to list all database in the current server:

SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)

It’ll show the following output:

+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.07 sec)Code language: JavaScript (javascript)

The output shows the 5 databases that include four system databases (information_schema, mysql, performance_schema, and sys) and a sample database classicmodels.

Besides the SHOW DATABASES command, MySQL also provides another command called SHOW SCHEMAS, which is a synonym of the SHOW DATABASES command:

SHOW SCHEMAS;Code language: SQL (Structured Query Language) (sql)

If you server has many databases and you want to find a specific database, you can use the LIKE clause as follows:

SHOW DATABASES LIKE pattern;Code language: SQL (Structured Query Language) (sql)

For example, the following statement returns the databases whose names end with the word 'schema':

SHOW DATABASES LIKE '%schema';Code language: JavaScript (javascript)

Output:

+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Querying databases from information_schema

If the condition of the LIKE 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;Code language: SQL (Structured Query Language) (sql)

The following SELECT statement retrieve the 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';Code language: SQL (Structured Query Language) (sql)

It returns the following result set:

+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| performance_schema |
| sys                |
| classicmodels      |
+--------------------+
4 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use SHOW DATABASES statement to list all database in the current server.
• Querying database names from the schemata table in in the information_schema database.