How to Show a List of All Databases in MySQL

MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995.

MySQL is reputed for its sturdy and quick functioning attributes which involve easy-to-handle features and dependability. MySQL can normally be seen together with dynamic web applications and is generally used to serve languages such as PHP but also other server-side programming languages like Python. In this article, you will discover how to list all the databases in MySQL along with some examples.

The MySQL, SHOW DATABASES is used to list the databases that are within the MySQL environment. It prints all the databases including the default system databases. It depends on the user whether they want to print all the databases or some specific databases. We can use ‘LIKE‘, and ‘WHERE‘ clauses to specify the databases that we want to print. There are 4 default system databases in the MySQL environment. Let’s study them one by one in detail:

information_schema:

• The information schema database is responsible for the metadata of the MySQL server and its databases, tables, columns, and other objects.
• It gives rise to information resources that are tables and columns, names, data types, character sets and privileges, etc.
  ,
• It is the most popular among database administrators and developers stored in the MySQL database so that they may know the information related to the structure and configuration of their servers.
• It is not intended to store the information; it is made to maneuver data around the database schema which is the skeleton of all the procedures and programming in the database.

mysql:

• MySQL is most useful to keep system-related data (e.g. user account details, user privileges, and metadata information about the administration).
• Likewise, it may incorporate 5 tables to wit: user, db, tables_priv, columns_priv, and host all hold the information about users, databases, and their permissions.
• Administrators, in turn, employ this database to perform user account management, password landing, and configuration of authentication settings.
• The core functionality of the project is to have the database created with MYSQL Database. We’ll manage user authentication and authorization with this database.

performance_schema:

• The performance_ schema database is the source of information related to the performance of the MySQL server’s operation and resource uses.
• It grabs these data and aggregates them into one place, covering server events, mutexes, file I/O, table vs index access patterns, and so on.
• The Database administrators and developers will use the performance_schema to monitor and analyze the MySQL server performance and detect the point of slowdown to the maximum efficiency of the queries and the configuration.
• It provides the analysis of [the] system, at the resource usage level, the periods of query execution times, and other performance metrics (at both server and session level).

sys:

• sys schema is a group of stored procedures, functions, and views that are not only critical in providing insights into the MySQL server’s performance but also provide a source of information as far as the server’s configuration is concerned.
• It is based on the structures, performance_schema and information_schema through which the statisticist can have a simplified stand with improved views for performance analysis and monitoring.
• Programming the sys schema with tools like sys.statement_analysis, sys.schema_statistics, sys.innodb_*, and so on (much more).
• It enables the convenient retrieval of issues related to, the metrics and guidelines on the tuning and avoiding of over-utilization resources by MySQL server.

Syntax:

SHOW {DATABASES | SCHEMAS}

[LIKE ‘pattern’ | WHERE expr];

Explanation:

In the above syntax, we are writing a query to list the databases. It is not compulsory to add the ‘LIKE‘ or the ‘WHERE‘ clause to reference the database. LIKE or WHERE clause is used when we want to list particular databases. Otherwise, if we do not use the LIKE or WHERE clause, all the databases will get listed.

Setting up the environment

Let’s first create some databases in MySQL.

CREATE database student;
CREATE database teacher;
CREATE database customer;
CREATE database orders;
CREATE database stud;

Example 1: Show/List all Databases

Let’s write a query to Show/List all the databases in MySQL.

Syntax:

Syntax 1:

SHOW databases;

Syntax 2:

SHOW schemas;

Query:

Query 1:

SHOW databases;

Query 2:

SHOW schemas;

Output:

Explanation: In the above example all the databases will be listed as we are not using the LIKE or WHERE clause. Both Let’squeries will give the same output. It will also print the default system databases.

Example 2: Show/List Databases using LIKE

Let’s write a query to Show/List all the databases starting with the letter ‘s‘.

Syntax:

Syntax 1:

SHOW databases LIKE pattern;

Syntax 2:

SHOW schemas LIKE pattern;

Query:

Query 1:

SHOW databases LIKE 's%';

Query 2:

SHOW schemas LIKE 's%';

Output:

Explanation: In the above example we are printing all the databases whose name starts with s. There are a total of 3 databases whose name starts with ‘s‘ including one system database. We are using LIKE keyword to specify the condition.

Example 3: Show/List Databases using WHERE clause

In MySQL, We cannot use the WHERE clause with SHOW DATABASES. Instead, we can fire the queries on the information_schema.SCHEMATA Table.

Let’s take an example of how we can use the ‘WHERE‘ clause to filter the database on some specific conditions.

Syntax:

SELECT schema_name

FROM information_schema.SCHEMATA

WHERE schema_name LIKE ‘Pattern’;

Let’s write a query to Show/List all the databases starting with the letter ‘s‘.

Query:

SELECT schema_name 
FROM information_schema.SCHEMATA 
WHERE schema_name LIKE 's%';

Output:

Explanation: In the above example we are printing all the schemas whose name starts with s. There are a total of 3 schemas whose name starts with ‘s‘ including one system schema. Here we cannot use the WHERE clause directly with SHOW DATABASE so we are using information_schema.SCHEMATA table to use the WHERE clause.

In summary, the SHOW DATABASES command in MySQL turns out to be a powerful tool either for the DBAs or for developers. It gives a short list of databases that are located on the dialed server instance enabling the users to have easy access to the suitable database for purposeful activities such as performing necessary operations and maintenance of objects.

To a great extent, this command has operated as an integrated element of the database monitoring process, solving problems and managing resources, while making new project purchases. Its ease of use and reliability make it a key encoding for any project involving MySQL, keeping data organized and well-managed without a need for complexity.