SQL Show Databases

Introduction

In this chapter, we will learn how to list all the databases available on your SQL server. This is useful for managing multiple databases and ensuring that the required databases are present. The SHOW DATABASES statement in SQL is used to display a list of all databases.

Listing Databases

The SHOW DATABASES statement is used to list all the databases on the SQL server.

Syntax

SHOW DATABASES;

Example

SHOW DATABASES;

This command will display a list of all databases available on the server.

Example Output

After executing the SHOW DATABASES statement, you might see an output like this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| mydatabase         |
| testdatabase       |
+--------------------+

Explanation

• information_schema: A system database that stores information about all other databases.
• mysql: A system database that contains information about users, privileges, and other administrative information.
• performance_schema: A system database used for performance monitoring.
• sys: A system database that provides simplified views for performance schema data.
• mydatabase and testdatabase: User-created databases.

Using SQL Workbench or Command Line

Using SQL Workbench

1. Open SQL Workbench.
2. Connect to Your SQL Server.
3. Execute the Query:
   • Enter the SHOW DATABASES; statement in the query editor.
   • Click the execute button or press Ctrl+Enter.

Using Command Line

1. Open Command Line Interface (CLI) or terminal.
2. Login to SQL Server:

   mysql -u username -p
   

   Enter your password when prompted.

3. Execute the Query:

   SHOW DATABASES;
   

Filtering Databases

In some SQL systems, you can filter the list of databases using the LIKE clause.

Syntax

SHOW DATABASES LIKE 'pattern';

Example

SHOW DATABASES LIKE 'test%';

This command will display all databases whose names start with "test".

Conclusion

The SHOW DATABASES statement is a simple yet powerful command that helps you manage and organize your SQL databases. By listing all the databases on your server, you can easily keep track of your database environment and ensure that everything is in order.