MySQL SHOW DATABASES Command Tutorial With Examples

In this tutorial, we will learn about syntax and usage of MySQL SHOW DATABASES command with programming examples.

We will also learn how to filter results using LIKE and WHERE clauses:

This command is used to list down the databases available on a MySQL server. The command also provides options to filter the list of databases through query expressions such as LIKE and WHERE.

Let us see how to use SHOW DATABASES in MySQL to show all databases and also to use it with LIKE and WHERE expressions.

=> Click here for the complete MySQL tutorial series

MySQL SHOW DATABASES

Syntax:

SHOW DATABASES [LIKE 'search_pattern | WHERE 'expression']

Let’s try to understand the Syntax. The LIKE and WHERE clauses are optional.

• LIKE is used to match a pattern. For example, listing databases whose name matches the specified pattern.
• WHERE is used to specify conditions depending on the columns displaying in the result set.

Both LIKE and WHERE are extensions to MySQL `SHOW` statement and can be applied to other commands like SHOW TABLES, SHOW COLUMNS, etc as well.

Note: Please note that the words DATABASES and SCHEMAS can be used interchangeably and are synonyms.

So, the commands SHOW DATABASES and SHOW SCHEMAS would yield similar results.

Permissions Required

The command SHOW DATABASES can be executed only for users that have GRANTS for the ‘SHOW DATABASES’ command. To view the grants for a user in MySQL, you can use the below command(s) :

SHOW GRANTS FOR 'root'@'localhost';

//Output

You can see in the above output that the user has the grant to SHOW DATABASES command, hence they can use it to fetch databases available on the current MySQL Server.

SHOW DATABASES Examples

Let’s try to understand the SHOW DATABASES command with the help of these examples.

Sample Data

Let’s add some sample schemas in the MySQL server using the commands below:

CREATE SCHEMA mysql_concepts;
CREATE SCHEMA mysql_ifelse;
CREATE SCHEMA mysql_transactions;
CREATE SCHEMA test_database;
CREATE SCHEMA test1;
CREATE SCHEMA test2;

Simple Without Any Clauses

SHOW DATABASES;
SHOW SCHEMAS;

//Output

You can see the output has a column named `Database` and it lists down all the databases that we created through the sample data.

Please note that there is a DB named ‘sys’, which is a system-level database and it exists as part of the MySQL installation and contains configuration information.

Using SHOW With LIKE Expression

Let’s now see an example using the LIKE expression along with the SHOW DATABASES command. Suppose we want to list the databases whose names start with ‘MySQL`.

We can get such a result using the LIKE expression.

SHOW DATABASES LIKE 'mysql%';

//Output

Using SHOW With WHERE Expression

Similar to LIKE, we can use WHERE expression to specify conditions against the resulting columns of the expression.
For the SHOW DATABASES command, we know there is only one column that’s returned as results and is named as `Database`. So, in order to use the WHERE clause, we can specify conditions on the column named `Database`.

Suppose we want to find out the names of all databases whose names are more than 5 characters long. We can use the WHERE clause to get such results.

SHOW DATABASES where LENGTH(`Database`) > 5;

//Output

In the above example, we have used the LENGTH() function to get the length of a STRING value and specified the condition in a WHERE clause for a column named `Database`.

SHOW DATABASES Through Command Line

We can also run the SHOW DATABASES through the command line in MySQL.

Steps include:

• Log in to the command/terminal with the user, which has grants/privileges to ‘SHOW DATABASES;` command.
• For logging in, we can use the below command on the terminal.

mysql -u root -p

• You will be prompted to enter the password for the account ‘root’. Enter the password and press ‘Enter’
• Once logged in, we can execute the SHOW DATABASES; command and view the output on the terminal window as below:

Frequently Asked Questions

Q #1) How can I see all MySQL databases?

Answer: MySQL provides a command named SHOW DATABASES, which would enable a user to view the names of all the databases available on the MySQL Server.

Please note, in order to view or execute this command, the user must possess GRANTS to the ‘SHOW DATABASES` command.

Syntax: SHOW DATABASES;

Q #2) How will you list all the databases in your schema?

Answer: In MySQL – SCHEMAS and DATABASES can be used interchangeably. Hence using the command SHOW DATABASES; or SHOW SCHEMAS; would return the same result – i.e. the list of all the databases or schemas.

Further reading =>> Comprehensive MySQL Commands Cheat Sheet

Conclusion

In this tutorial, we learned about the SHOW DATABASES command, which is used to fetch the names of the databases available in the MySQL server. We also saw different examples using this command and how we can apply filters using LIKE and WHERE clauses to get a filtered list of database names.

Suggested reading =>> MySQL SHOW USER Command with examples