MySQL SHOW DATABASES: List and Filter Databases

SHOW DATABASES lists the databases on the server that your account is allowed to see.

query.sql

SQL

1
2
3

CREATE DATABASE shop;
CREATE DATABASE blog;
SHOW DATABASES;

Output

OUTPUT

+--------------------+
| Database           |
+--------------------+
| blog               |
| information_schema |
| learn              |
| mysql              |
| performance_schema |
| shop               |
| sys                |
+--------------------+
7 rows in set

You see your own databases, learn, shop and blog, and four built-in ones: information_schema, mysql, performance_schema and sys. The System Databases lesson explains them.

Filter the list

LIKE uses wildcards: % for any text, _ for one character.

query.sql

SQL

SHOW DATABASES LIKE 's%';

Output

OUTPUT

+---------------+
| Database (s%) |
+---------------+
| shop          |
| sys           |
+---------------+
2 rows in set

WHERE accepts a full condition on the column Database:

query.sql

SQL

SHOW DATABASES WHERE `Database` NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

Output

OUTPUT

+----------+
| Database |
+----------+
| blog     |
| learn    |
| shop     |
+----------+
3 rows in set

The standard-SQL way

SHOW DATABASES is MySQL syntax. The portable way uses INFORMATION_SCHEMA, which works like any table and can be sorted, joined and filtered:

query.sql

SQL

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name IN ('shop', 'blog')
ORDER BY schema_name;

Output

OUTPUT

+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| blog        | utf8mb4                    | utf8mb4_0900_ai_ci     |
| shop        | utf8mb4                    | utf8mb4_0900_ai_ci     |
+-------------+----------------------------+------------------------+
2 rows in set

You only see what you may see

An account without privileges on a database does not see it in the list. If your user cannot see a database you know exists, it is a permission issue, not a missing database.

query.sql

SQL

SELECT COUNT(*) AS visible_databases FROM information_schema.schemata;

Output

OUTPUT

+-------------------+
| visible_databases |
+-------------------+
|                 7 |
+-------------------+
1 row in set

lightbulb

In scripts

To check from SQL whether a database exists, query information_schema.schemata. To create it safely, use CREATE DATABASE IF NOT EXISTS.