# 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 familiarise 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](https://www.ionos.co.uk/digitalguide/server/know-how/what-is-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:

```sql
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**:

```sql
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:

```sql
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, time-outs 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:

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

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

### To show only user-defined databases

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

```sql
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.

Bear in mind 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.


This is a markdown version of: [https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-show-databases/](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-show-databases/) for AI/LLM consumption.