MySQL SHOW DATABASES

The SHOW DATABASES statement lists all the databases currently available on the connected MySQL server. It is one of the most frequently used commands when navigating a MySQL server, as it gives you an instant overview of what databases exist before you start working with them.

Syntax

SHOW DATABASES;

That is the entire statement — no additional parameters are required. MySQL will return a single-column result set listing every database the current user has permission to see.

Basic Example

Run the following in your MySQL client or Workbench:

SHOW DATABASES;

A typical result on a fresh MySQL installation might look like:

Database
information_schema
mysql
performance_schema
school
sys

The first four entries (information_schema, mysql, performance_schema, sys) are system databases created automatically by MySQL. The school database in this example is one we created ourselves.

System Databases: Never modify or delete the built-in system databases (mysql, information_schema, performance_schema, sys). They store MySQL's own configuration, user accounts, and performance data. Altering them can break your entire MySQL server.

Filtering with LIKE

If your server has many databases, you can narrow the results using a LIKE pattern. The % wildcard matches any sequence of characters:

-- Show only databases whose names start with 's'
SHOW DATABASES LIKE 's%';

-- Show only databases whose names contain 'shop'
SHOW DATABASES LIKE '%shop%';

This is handy when you are managing a server with dozens of databases and need to find one quickly.

SHOW SCHEMAS — An Alias

Just like with CREATE and DROP, MySQL treats DATABASE and SCHEMA as the same thing. These two commands produce identical output:

SHOW DATABASES;
SHOW SCHEMAS;

Checking Databases via information_schema

For more advanced filtering or programmatic use, you can query the information_schema system database directly. It stores metadata about all databases and tables on the server:

SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

This gives you the same list as SHOW DATABASES but in a standard SQL format that can be combined with more complex WHERE conditions and JOIN queries.

Tip: The SHOW DATABASES command only shows databases that the currently logged-in MySQL user has permission to access. If you are logged in as a limited user and cannot see a database you created, check the user's privileges.

Key Points to Remember

SHOW DATABASES; lists all databases visible to the current user.
SHOW SCHEMAS; is an alias — it does the same thing.
MySQL comes with built-in system databases — do not modify or delete them.
Use LIKE '%pattern%' to filter results when there are many databases.
You can also query information_schema.schemata for more control over the results.

« Previous Lesson Next Lesson »