SQL DATABASES

What does the SQL DATABASES keyword do?

Lists the names (and optional metadata) of all databases in the current MySQL or MariaDB server.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DATABASES: Native: MySQL, MariaDB. Partial equivalents: PostgreSQL (\l or SELECT FROM pg_database), SQL Server (sys.databases), Oracle (SELECT * FROM v$database), SQLite (PRAGMA database_list).

SQL DATABASES Full Explanation

DATABASES is most often encountered in the MySQL-family statement SHOW DATABASES, which returns a result set containing every logical database (also called a schema) that lives inside the running server instance. The statement reads from the system catalog, so results reflect the latest committed state. On privileged accounts the list is complete; on limited accounts it contains only databases the user can access. Optional LIKE or WHERE clauses narrow the result set with pattern matching or arbitrary expressions against the INFORMATION_SCHEMA.SCHEMATA columns. Some vendors call databases “schemas,” so other dialects expose the same information through different keywords or system tables rather than the DATABASES keyword itself.

SQL DATABASES Syntax

SHOW DATABASES;
SHOW DATABASES LIKE 'pattern';
SHOW DATABASES WHERE expression;

SQL DATABASES Parameters

pattern (STRING) - Optional pattern with % and _ wildcards evaluated against database names.
expression (SQL expression) - Optional boolean condition referencing INFORMATION_SCHEMA.SCHEMATA columns (e.g., DEFAULT_CHARACTER_SET_NAME).

Example Queries Using SQL DATABASES

-- List every database the current user can see
SHOW DATABASES;

-- List databases that start with 'analytics'
SHOW DATABASES LIKE 'analytics%';

-- List UTF8 databases only
SHOW DATABASES WHERE DEFAULT_CHARACTER_SET_NAME = 'utf8mb4';

Expected Output Using SQL DATABASES

A result grid with one row per database
The single unnamed column contains database names when no WHERE clause is used
With WHERE, all INFORMATION_SCHEMA
SCHEMATA columns are returned so additional metadata (charset, collation, etc
) becomes visible

Use Cases with SQL DATABASES

Audit databases after a migration
Verify that a new CREATE DATABASE statement succeeded
Feed DevOps scripts that iterate over all schemas for backups or maintenance
Help new team members discover available environments (dev, staging, production)

Common Mistakes with SQL DATABASES

Assuming SHOW DATABASES works in PostgreSQL or SQL Server (it does not)
Forgetting that privileges filter results, leading to a seemingly incomplete list
Using single quotes incorrectly in the LIKE pattern
Expecting WHERE to work without the INFORMATION_SCHEMA privilege

First Introduced In

MySQL 3.22

Frequently Asked Questions

How do I list only databases that match a pattern?

Use LIKE with wildcards:```SHOW DATABASES LIKE 'test%';```

Why do I see fewer databases than my colleague?

SHOW DATABASES respects privileges. Your user can only see databases for which it has some permission. Ask an administrator for the SHOW DATABASES or global SHOW privilege.

Does SHOW DATABASES lock anything?

No. The command is read-only and does not lock databases or tables.

What is the difference between SHOW SCHEMAS and SHOW DATABASES?

They are synonyms in MySQL. Both return the same result set.