Using MySQL SHOW DATABASES

The SHOW DATABASES command is used to list all databases on a MySQL server host, as seen below:

SHOW DATABASES;

‍

To list all databases on the local MySQL database server, for example, you must first log in to the database server as follows:

>mysql -u root -p
Enter password: **********
mysql>

‍

Then, using the SHOW DATABASES command, show the following databases:

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

Because the SHOW SCHEMAS command is synonymous with SHOW DATABASES, the following command yields the same result as the one above:

SHOW SCHEMAS;

‍

If you wish to query the database for records that match a certain pattern, use the LIKE clause as shown below:

SHOW DATABASES LIKE pattern;

‍

For example, the following command returns a database with the string'schema' at the end;

SHOW DATABASES LIKE '%schema';

+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

It's worth noting that if you started the MySQL database server with --skip-show-database, you won't be able to use the SHOW DATABASES command unless you have the SHOW DATABASES access.

Using information_schema to query database data

If the LIKE clause condition is insufficient, you can directly query the database information from the schemata table in the information schema database.
The following query, for example, yields the same results as the SHOW DATABASES command.

SELECT schema_name 
FROM information_schema.schemata;

The SELECT command below retrieves databases whose names finish in 'schema' or 's'.

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%schema' OR 
      schema_name LIKE '%s';

It returns the following result set:

+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| performance_schema |
| sys                |
| classicmodels      |
+--------------------+
4 rows in set (0.00 sec)

In this article, you learned how to display all databases on the MySQL server by using the SHOW DATABASES command or querying the schemata table in the information schema database. To fully maximize and optimize your database management, Ottomatik offers a wide range of automated tools to help you on your daily tasks on database management. Try Ottomatik for free and experience hassle-free database management!