dbForge Studio for MySQL

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 SHEMAS;

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_nameFROM 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.

Useful tips and best practices

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

SELECT DATABASE();

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!

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

Context-sensitive code completion

Automatic SQL syntax check

Security Manager to administer user accounts and privileges

Cutting-edge MySQL IDE for database development and management

Download

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.

Thank you for subscribing to our monthly digest!

Your subscription is now complete.