Summary: in this tutorial, you will learn how to use the MariaDB show databases statement to list all databases from the server.
Introduction to MariaDB show databases statement
The show databases statement allows you to list the databases on a MariaDB server.
The following illustrates the syntax of the show databases statement:
show databases
[like 'pattern' | where search_expression];
Code language: SQL (Structured Query Language) (sql)In this syntax, the like or where clause specifies a condition to search for the databases. They cannot be used at the same time.
If you skip the like or where clause, the show databases statement lists all databases in the MariaDB server.
Notice that the show databases statement only returns the databases that you have some kind of privilege unless you have the global show databases privilege.
The show schemas is a synonym for show databases, therefore, you can use them interchangeably.
MariaDB show databases statement examples
Let’s take some examples of using the show databases statement.
A) MariaDB show databases example
First, show all databases in the server:
show databases;
Code language: SQL (Structured Query Language) (sql)
Then, create a new database called testdb:
create database testdb;
Code language: SQL (Structured Query Language) (sql)Finally, use the show databases statement again:
show databases;
Code language: SQL (Structured Query Language) (sql)
B) MariaDB show databases statement with the like clause example
The following statement uses the like clause to find the database with the name nation:
show databases
like 'nation';
Code language: SQL (Structured Query Language) (sql)
The following example uses the like clause to show all databases whose names end with the word schema:
show databases
like '%schema';
Code language: SQL (Structured Query Language) (sql)
C) MariaDB show databases with the where clause example
The following example shows how to use the where clause to find user-created databases:
show databases
where `database` not in
('information_schema',
'mysql',
'performance_schema');
Code language: SQL (Structured Query Language) (sql)
It is important to put the `database` under the backquotes ( `), because it is a keyword in MariaDB; or you will get an error.
In this tutorial, you learned how to use the MariaDB show databases statement to list all databases from the server.
