Show All Databases in MySQL: Easy Ways

List Databases Mysql

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 -p
Code language: SQL (Structured Query Language) (sql)
Log In To Mysql
Log In To Mysql
  • 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.
Show Databases
Show Databases

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)
Show Databases Like
Show Databases Like

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)
Show Schemas
Show Schemas

You can use the LIKE operator with this command as well.

SHOW SCHEMAS LIKE '%a%';
Code language: SQL (Structured Query Language) (sql)
Show Schemas Like
Show Schemas Like

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)
Show Databases From Schemata
Show Databases From Schemata

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)
Select Schema Name From Schemata
Select Schema Name From Schemata

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)
Using Where Clause On Schemata
Using Where Clause On Schemata

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.

View Databases From Workbench
View Databases From Workbench

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.

View All Databases From Workbench
View All Databases From Workbench

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.