How to use MariaDB SHOW DATABASES to list databases

MariaDB SHOW DATABASES displays all available databases on a MariaDB server. It’s a quick way to familiarize yourself with a new environment or keep track of existing structures.

How does the SHOW DATABASES command work?

The SHOW DATABASES command is one of the core tools in MariaDB. It lists all databases on the connected server that are visible to the current user. Accounts with limited permissions will only see the databases they can access. In multi-user systems or development environments with many projects, this command is a quick way to see what’s available without having to run complex queries or open additional tools. Behind the scenes, MariaDB obtains the list of databases from the internal information_schema database, which contains all metadata about databases and their objects. SHOW DATABASES is often the first command you run after connecting to a server — whether you’re troubleshooting, exploring a new setup or preparing to import data.

What is the syntax of MariaDB SHOW DATABASES?

The MariaDB SHOW DB command’s syntax is very simple:

SHOW DATABASES;

MariaDB then returns all databases in a table, with each row representing one database. The list includes system databases like mysql, information_schema, performance_schema, as well as any user-created databases.

You can filter the results using an optional LIKE clause:

SHOW DATABASES LIKE 'project_%';

This example limits the output to databases whose names start with “project.” The results depend on your user account’s permissions.

What parameters and alternatives are there for SHOW DATABASES?

Although SHOW DATABASES may seem simple, it can be expanded using basic filters. In addition to LIKE, you can use a WHERE clause with extended SQL syntax:

SHOW DATABASES WHERE `Database` LIKE '%test%';

This displays all databases with names containing “test.”

SHOW DATABASES is part of a larger group of MariaDB SHOW commands that support administrative and diagnostic tasks. Common examples include:

• SHOW TABLES: lists all tables in the current database
• SHOW COLUMNS FROM table_name: displays details about all columns in a specific table
• SHOW CREATE TABLE table_name: shows the SQL statement used to create the table
• SHOW VARIABLES: lists server configuration values, such as buffer sizes, timeouts or character sets
• SHOW STATUS: provides runtime information about the server’s current state
• SHOW PROCESSLIST: shows active connections and running SQL commands

These related commands help you troubleshoot issues, monitor performance and manage your databases more effectively.

What are some uses for MariaDB SHOW DATABASES?

MariaDB SHOW DATABASES is useful in many day-to-day situations — from exploring a new server to searching for specific databases or checking server status. Here are some practical examples:

To filter databases by specific name patterns

If you need to find all databases that start with “customers” and also contain “eu”, use the following command:

SHOW DATABASES
WHERE `Database` LIKE 'customers%' AND `Database` LIKE '%eu%';

This narrows down the results, so you don’t have to scroll through the entire list.

To show only user-defined databases

You can exclude system databases with a WHERE clause to focus only on user-defined ones:

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

This displays only the databases created for projects, applications or tests. System databases like mysql, information_schema, and performance_schema store management data and are usually not relevant for everyday tasks. By using NOT IN, you can exclude these internal databases.

Note that using WHERE with SHOW DATABASES works in MariaDB, but not in MySQL. Also, keep in mind that the column name Database is case sensitive and backticks are required as it is a reserved word.