MySQL Show/List Databases

When we work with the MySQL server, it is a common task to show or list the databases, displaying the table from a particular database, and information of user accounts and their privileges that reside on the server. In this article, we are going to focus on how to list databases in the MySQL server.

We can list all the databases available on the MySQL server host using the following command, as shown below:

Open the MySQL Command Line Client that appeared with a mysql> prompt. Next, log in to the MySQL database server using the password that you have created during the installation of MySQL. Now, you are connected to the MySQL server host, where you can execute all the SQL statements. Finally, run the SHOW Databases command to list/show databases.

We can see the following output that explains it more clearly:

MySQL also allows us another command to list the databases, which is a SHOW SCHEMAS statement. This command is the synonyms of the SHOW DATABASES and gives the same result. We can understand it with the following output:

List Databases Using Pattern Matching

Show Databases command in MySQL also provides an option that allows us to filter the returned database using different pattern matching with LIKE and WHERE clause. The LIKE clause list the database name that matches the specified pattern. The WHERE clause provides more flexibility to list the database that matches the given condition in the SQL statement.

Syntax

The following are the syntax to use pattern matching with Show Databases command:

We can understand it with the example given below where percent (%) sign assumes zero, one, or multiple characters:

The above statement will give the following output:

Sometimes the LIKE clause is not sufficient; then, we can make a more complex search to query the database information from the schemata table in the information schema. The information schema in MySQL is an information database so that we can use it to get the output using the SHOW DATABASES command.

This statement will give the same result as the SHOW DATABASES command:

Now, we are going to see how we can use the WHERE clause with the SHOW DATABASES command. This statement returns the database whose schema name starts with "s":

It will give the following output:

NOTE: It is to be noted that if the MySQL server started with the "--skip-show-database" option, we could not use the SHOW DATABASES command unless we have the SHOW DATABASES privilege.