How to Get a List of all Databases in MySQL

Tutorialsrack 05/10/2019 MySQL

In this article, we will learn how to get a list of all the databases in MySQL.

There are some ways to get the list of all the databases in MySQL. Here we are using these queries to get the list of all the databases on MySQL Workbench.

By Using SHOW DATABASES

To get the list of all the database by using SHOW DATABASES, a query is as follows:

Example - By using SHOW DATABASES

SHOW DATABASES;

If you want to query the databases that match a specific pattern, then you use the LIKE clause as follows:

Syntax - SHOW DATABASES with LIKE operator

SHOW DATABASES LIKE pattern;

Example - SHOW DATABASES with LIKE Operator

SHOW DATABASES LIKE '%schema';

Note:- If the MySQL database server started with --skip-show-database, you cannot use the SHOW DATABASES statement unless you have the SHOW DATABASES privilege.

By Using SHOW SCHEMAS

The SHOW SCHEMAS statement is a synonym for SHOW DATABASES, therefore the following statement returns the same result as the SHOW DATABASES returns. To get the list of all the database by using SHOW SCHEMAS, a query is as follows:

Example - Using SHOW SCHEMAS

SHOW SCHEMAS;

By Using INFORMATION_SCHEMA

To get the list of all the database by using INFORMATION_SCHEMA, a query is as follows:

Example - Using INFORMATION_SCHEMA

SELECT SCHEMA_NAME as DATABASE_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY SCHEMA_NAME;

I hope this article will help you to understand how to get a list of all the databases in MySQL.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Tagged: MySQL SQL SELECT Statement MySQL SHOW Statement