How to List Databases in MySQL

Galaxy Glossary

How do I list all databases in MySQL?

SHOW DATABASES returns all databases the MySQL server knows about, optionally filtered with LIKE or WHERE.

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

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does SHOW DATABASES do?

SHOW DATABASES queries the information_schema and displays every database your MySQL server tracks. The command helps you confirm existing databases before creating, dropping, or switching to one.

How to list every database?

Run SHOW DATABASES; in any MySQL client. The result set contains a single column named Database listing all databases you have privileges to view.

How to filter databases by pattern?

Add a LIKE clause: SHOW DATABASES LIKE 'sales%';. Wildcards let you narrow results to names that start, end, or contain a specific string.

Can I use WHERE for more complex filters?

Yes. Use SHOW DATABASES WHERE `Database` NOT LIKE '%test%'; for advanced filtering with logical operators, comparisons, or NOT clauses.

How to see only databases you can access?

The list already respects your privileges. To confirm, connect as the intended user and re-run SHOW DATABASES;. Databases outside your grants disappear from the result.

Best practices for listing databases

1) Always include a semicolon to terminate the statement. 2) Use precise patterns to avoid overlooking similarly named databases. 3) Combine SHOW DATABASES with USE db_name; to switch quickly after inspection.

Use case: verifying migration

After restoring backups, run SHOW DATABASES; to ensure the expected schemas exist before pointing applications to the new server.

Why How to List Databases in MySQL is important

How to List Databases in MySQL Example Usage


-- After spinning up a fresh environment, verify production and analytics databases exist
SHOW DATABASES LIKE 'ecommerce_prod';
SHOW DATABASES LIKE 'ecommerce_analytics';

How to List Databases in MySQL Syntax


SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr];

-- Examples in an ecommerce context
-- List every database
SHOW DATABASES;

-- Find staging databases used for QA
SHOW DATABASES LIKE 'ecommerce_staging%';

-- Exclude test databases
SHOW DATABASES WHERE `Database` NOT LIKE '%_test';

Common Mistakes

Omitting the semicolon: MySQL treats the line as incomplete, waiting for more input. Always end with <code>;</code>.
Quoting LIKE patterns with backticks instead of single quotes: MySQL interprets backticks as identifiers, leading to empty results. Use single quotes.