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;
Code language: SQL (Structured Query Language) (sql)
For example, to list all database in the local MySQL database server, first login to the database server as follows:
>mysql -u root -p
Enter password: **********
mysql>
Code language: SQL (Structured Query Language) (sql)
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)
Code language: SQL (Structured Query Language) (sql)
The SHOW SCHEMAS command is a synonym for SHOW DATABASES, therefore the following command returns the same result as the one above:
SHOW SCHEMAS;
Code language: SQL (Structured Query Language) (sql)
If you want to query the database that matches a specific pattern, you use the LIKE clause as follows:
SHOW DATABASES LIKE pattern;
Code language: SQL (Structured Query Language) (sql)
For example, the following statement returns database that ends with the string 'schema';
SHOW DATABASES LIKE '%schema';
+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
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 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 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';
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.00 sec)
Code language: SQL (Structured Query Language) (sql)
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.