{"id":571,"date":"2019-10-26T17:24:06","date_gmt":"2019-10-26T10:24:06","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=571"},"modified":"2020-04-11T23:23:56","modified_gmt":"2020-04-11T16:23:56","slug":"mariadb-show-databases","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-show-databases\/","title":{"rendered":"MariaDB Show Databases"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB show databases<\/code> statement to list all databases from the server.<\/p>\n\n\n\n

Introduction to MariaDB show databases statement<\/h2>\n\n\n\n

The show databases<\/code> statement allows you to list the databases on a MariaDB server.<\/p>\n\n\n\n

The following illustrates the syntax of the show databases<\/code> statement:<\/p>\n\n\n

show<\/span> databases<\/span>\n[like<\/span> 'pattern'<\/span> | where<\/span> search_expression];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the like<\/a><\/code> or where<\/a><\/code> clause specifies a condition to search for the databases. They cannot be used at the same time.<\/p>\n\n\n\n
If you skip the like<\/code> or where<\/code> clause, the show databases<\/code> statement lists all databases in the MariaDB server.<\/p>\n\n\n\n
Notice that the show databases<\/code> statement only returns the databases that you have some kind of privilege unless you have the global show databases<\/code> privilege.<\/p>\n\n\n\n
The show schemas<\/code> is a synonym for show databases<\/code>, therefore, you can use them interchangeably.<\/p>\n\n\n\n
MariaDB show databases statement examples<\/h2>\n\n\n\n
Let’s take some examples of using the show databases<\/code> statement.<\/p>\n\n\n\n
A) MariaDB show databases example<\/h3>\n\n\n\n
First, show all databases in the server:<\/p>\n\n\n
show<\/span> databases<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
Then, create a new database<\/a> called testdb<\/code>:<\/p>\n\n\n
create<\/span> database<\/span> testdb;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, use the show databases<\/code> statement again:<\/p>\n\n\n
show<\/span> databases<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
B) MariaDB show databases statement with the like clause example<\/h3>\n\n\n\n
The following statement uses the like<\/code> clause to find the database with the name nation<\/code>:<\/p>\n\n\n
show<\/span> databases<\/span> \nlike<\/span> 'nation'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
The following example uses the like<\/code> clause to show all databases whose names end with the word schema<\/code>:<\/p>\n\n\n
show<\/span> databases<\/span> \nlike<\/span> '%schema'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
C) MariaDB show databases with the where clause example<\/h3>\n\n\n\n
The following example shows how to use the where<\/code> clause to find user-created databases:<\/p>\n\n\n
show<\/span> databases<\/span> \nwhere<\/span> `database`<\/span> not<\/span> in<\/span> \n('information_schema'<\/span>,\n'mysql'<\/span>,\n'performance_schema'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
It is important to put the `database`<\/code> under the backquotes ( `<\/code>), because it is a keyword in MariaDB; or you will get an error.<\/p>\n\n\n\n
In this tutorial, you learned how to use the MariaDB show databases<\/code> statement to list all databases from the server.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t