What is the command to check database in mysql?

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

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.

Conclusion

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

How do I see my MySQL database?

How do I list a database in MySQL?

How can I see all SQL databases?

How do I find MySQL database in terminal?