MariaDB SHOW DATABASES

MariaDB SHOW DATABASES displays all available databases on a MariaDB server. It’s a quick way to fa­mil­iar­ize yourself with a new en­vi­ron­ment or keep track of existing struc­tures.

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 per­mis­sions will only see the databases they can access. In multi-user systems or de­vel­op­ment en­vi­ron­ments with many projects, this command is a quick way to see what’s available without having to run complex queries or open ad­di­tion­al 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 con­nect­ing to a server — whether you’re trou­bleshoot­ing, 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 rep­re­sent­ing 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 per­mis­sions.

What pa­ra­me­ters and al­ter­na­tives 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 con­tain­ing “test.”

SHOW DATABASES is part of a larger group of MariaDB SHOW commands that support ad­min­is­tra­tive and di­ag­nos­tic 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 con­fig­u­ra­tion values, such as buffer sizes, timeouts or character sets
• SHOW STATUS: provides runtime in­for­ma­tion about the server’s current state
• SHOW PROCESSLIST: shows active con­nec­tions and running SQL commands

These related commands help you trou­bleshoot issues, monitor per­for­mance and manage your databases more ef­fec­tive­ly.

What are some uses for MariaDB SHOW DATABASES?

MariaDB SHOW DATABASES is useful in many day-to-day sit­u­a­tions — 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, ap­pli­ca­tions or tests. System databases like mysql, information_schema, and performance_schema store man­age­ment 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.