MySQL SHOW DATABASES: List All Databases in Different Ways

As a DBA or MySQL developer, you might need to get the list of all MySQL databases that reside on a server. There may be various reasons for that, for example, to get familiar with the environment or to perform server maintenance.

In this article, we provide a detailed guide on how to show all databases in MySQL and filter them according to certain criteria. Read on to find out about:

MySQL SHOW DATABASES syntax
SHOW SCHEMAS command to view MySQL databases
Permissions required to get the list of databases
How to filter the databases list
How to show databases in command line
How to get and manage database list with MySQL GUI tool

MySQL SHOW DATABASES command to get list of databases

Run the following query to show list of databases:

SHOW DATABASES;

You can run this statement from MySQL Command Line Client, MySQL Shell, as well as from any GUI tool that supports SQL—for example, dbForge Studio for MySQL.

MySQL returns the results in a table with one column—Database. The databases are ordered in alphabetical order. The summary line tells you how many rows (or databases) there are.

Note
Unless you have the global SHOW DATABASES privilege, you will see only those databases you have some kind privilege for. You can also grant all privileges in MySQL for the required user so that they could see all the databases on a server.

SHOW SCHEMAS to view MySQL databases

The SHOW SCHEMAS is an alternative MySQL command to view databases on a server host.

SHOW SCHEMAS;

Similar to MySQL SHOW DATABASES statement, SHOW SCHEMAS can be run from MySQL Command Line Client, MySQL Shell, and dbForge Studio for MySQL.

The outputs returned by MySQL will be identical.

Filtering databases list using SHOW DATABASES LIKE

There might be a lot of databases on the server, and the list can be quite large. In this case, you may benefit from using the LIKE expression along with the MySQL SHOW DATABASES command:

SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expression]

Suppose, we need to get the list of all databases that start with S. The query will look like as follows:

SHOW DATABASES LIKE 's%';

To list the databases the names of which are more than 7 characters long, we run the following query:

SHOW DATABASES where LENGTH(`Database`) > 7;

How to show list of all databases in MySQL Command line

You can also list all databases using command line. There are three common methods for this.

1. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then connect to the server using the mysql -u root -p command. Enter the password and execute the SHOW DATABASES; command we have discussed above.

2. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then run the following query:

mysql -u user -p -e "show databases;"

3. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then run the query:

mysqlshow -u user -p

List Databases by information_schema

There is another popular way to get the list of databases in MySQL—to query the required information directly from the schemata table of the MySQL information_schema database. You will need to execute the MySQL SELECT query

For example, the following statement returns the same result as the SHOW DATABASES; command.

SELECT schema_name
FROM information_schema.schemata;

And again, the query can be run from MySQL Command Line Client, MySQL Shell, and dbForge Studio for MySQL.

Display and manage database list with dbForge Studio

dbForge Studio for MySQL is a universal all-in-one GUI tool bound to solve all possible database development, management, and administration tasks. With the Studio, you can work with MySQL and MariaDB databases in an intuitive well-designed interface.

How to view all MySQL databases in dbForge Studio

After you have connected to the server, the databases that are hosted on it will be displayed in Database Explorer. To view the database tables just expand the database and tables nodes.
All the commands we have mentioned above work well from the Studio's Code Editor equipped with impeccable syntax check and context-sensitive code completion.

More than that, dbForge Studio comes with the advanced Security manager functionality that allows for full control of MySQL user accounts, their roles and privileges.

dbForge Studio for MySQL - manage and see all databases on Windows

Useful tips and best practices

To find out which database is currently selected, use the following query:

SELECT DATABASE();

You can read more about this statement on the MySQL Select Database page. In dbForge Studio the selected database will be shown in the menu ribbon. You can easily switch between the databases using the dropdown.

To find out what tables the selected database contains, execute:

SHOW TABLES;

In dbForge Studio the database tables can be displayed by expanding the nodes in Database Explorer. Also, you can use MySQL syntax to show tables/show tables list.

Conclusion

dbForge Studio for MySQL is one of the best MySQL GUI tools on the market today. It encompasses dozens of useful features that allow you to perform any database-related tasks: create a database in MySQL, back up a MySQL database, conduct MySQL performance monitoring and many, many more. However, a picture paints a thousand words, so we welcome you to download a FREE fully functional 30-day trial of dbForge Studio for MySQL and check it in action!