In this tutorial, we will see how can we list all the databases in MySQL using CLI as well as the MySQL workbench. There are multiple ways you can list all the databases using CLI. We will see all the available methods out there. So, let’s get started!
Also read: MySQL Show Users – A Complete Guide
List Databases using CLI
As said earlier, there are multiple methods you can list out all the databases in your MySQL server. Let’s see them one by one.
Using the SHOW DATABASES
This is the easiest method to list all the databases in your MySQL server. Follow the instructions below to list the databases.
- Login from the MySQL command line client using the following command-
-- for windows
mysql -u root -p
-- for linux
sudo mysql -u root -pCode language: SQL (Structured Query Language) (sql)
- After that, you will be asked to enter the MySQL password. Enter the password and hit enter to log in.
- Now, enter the command the SHOW DATABASES.
SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)- Note that, it’s DATABASES and not DATABASE.
- It will show all the databases available in your system.
You can also use the LIKE operator to list the databases based on the pattern.
For example,
SHOW DATABASES LIKE '%j%';Code language: SQL (Structured Query Language) (sql)
As you can see, it will list all the databases as per your mentioned condition.
Using the SHOW SCHEMAS
The SHOW SCHEMAS is an alternative and the synonym for the SHOW DATABASES. Therefore, it will return the same result.
Let’s see.
SHOW SCHEMAS;Code language: SQL (Structured Query Language) (sql)
You can use the LIKE operator with this command as well.
SHOW SCHEMAS LIKE '%a%';Code language: SQL (Structured Query Language) (sql)
As you can see, it shows the perfect result.
Using the information_schema Database
The information_schema database, which comes with the installation, is the place where all the metadata of your MySQL server such as all the databases, tables, stored programs, users, privileges, etc is stored.
Therefore, we can use this database to get the list of all available databases on our server.
The ‘schemata’ table in the information_schema database contains all the databases’ names and their metadata.
Let’s write a query.
USE information_schema;
SELECT * FROM schemata;Code language: SQL (Structured Query Language) (sql)
As you can see, all the databases in our MySQL server are listed here.
To list only databases’ names without other information, just select that particular column name.
SELECT SCHEMA_NAME FROM schemata;Code language: SQL (Structured Query Language) (sql)
You can also use the WHERE clause and the LIKE operator on this table to get the desired output.
For example,
SELECT SCHEMA_NAME FROM schemata
WHERE schema_name LIKE '%a%';Code language: SQL (Structured Query Language) (sql)
As you can see, we got the correct output.
Show Database List using Workbench
If you are using the workbench, you won’t need to write any query to list the databases’ names.
Simply open the workbench and on the left-hand side, you will see all the database names.
Note that, internal schemas are kept hidden by default in the workbench.
To view the internal schemas, simply click on-
Edit -> Preferences -> SQL Editor and then check the box “Show Metadata and Internal Schemas”
After that, close the window and click on the refresh icon on the right side of the schemas in the left sidebar.
As you can see, all the databases are visible now.
Summary
In this tutorial, we learned to show all databases in our MySQL server using multiple methods. I hope you have learned new methods in this tutorial. If you have, don’t forget to share it with your friends and let them learn too.