The SHOW DATABASES command is used to list all databases on a MySQL server host, as seen below:
To list all databases on the local MySQL database server, for example, you must first log in to the database server as follows:
Then, using the SHOW DATABASES command, show the following databases:
Because the SHOW SCHEMAS command is synonymous with SHOW DATABASES, the following command yields the same result as the one above:
If you wish to query the database for records that match a certain pattern, use the LIKE clause as shown below:
For example, the following command returns a database with the string'schema' at the end;
It's worth noting that if you started the MySQL database server with --skip-show-database, you won't be able to use the SHOW DATABASES command unless you have the SHOW DATABASES access.
Using information_schema to query database data
If the LIKE clause condition is insufficient, you can directly query the database information from the schemata table in the information schema database.
The following query, for example, yields the same results as the SHOW DATABASES command.
The SELECT command below retrieves databases whose names finish in 'schema' or 's'.
It returns the following result set:
In this article, you learned how to display all databases on the MySQL server by using the SHOW DATABASES command or querying the schemata table in the information schema database. To fully maximize and optimize your database management, Ottomatik offers a wide range of automated tools to help you on your daily tasks on database management. Try Ottomatik for free and experience hassle-free database management!