List the Databases in SQL

Unfortunately, various database engines provide different mechanisms for retrieving the list of databases. Therefore, there is no universal technique for retrieving the databases as it is dependent on the target RDBMS.

In this tutorial, we will cover the various methods and techniques that you can use to list the available databases in a given database server. We will cover the various database engines such as MySQL, PostgreSQL, SQL Server, and SQLite.

Requirements:

Before we begin listing the databases, ensure that you have the following:

• Installed and running SQL database server
• Appropriate permissions to access the server and list databases

It is good to keep in mind that the output may also vary depending on the specific method and database engine.

List the Databases in MySQL

Let us start with one of the most popular relational database systems. In MySQL, we can use the SHOW DATABASES command in the MySQL shell.

Start by connecting to the target MySQL server as shown in the following example command:

This should connect to the database server as the root user. Provide the password for the specified username to connect to the server.

Once connected, you can run the SHOW DATABASES command to list the available databases in the server as follows:

This query lists all the available databases on your MySQL server.

An example output is as follows:

Method 1: Information Schema

If you do not have access to the MySQL shell, you can also use the native SQL queries by fetching an information from the “information_schema” database. The “information_schema” database is one of the most powerful and useful built-in databases in MySQL. It contains all the information and metadata about all the objects in the server.

To retrieve the list of available databases in the server, we can query the database as follows:

The previous query retrieves the names of all the databases that are stored in the “information_schema” database.

An example output is as follows:

List the Databases in PostgreSQL

Let us move on to the next most popular relational database system: PostgreSQL. In PostgreSQL, we can use the “\l” command in the PSQL command-line utility.

The command is as follows:

The command returns a list of all the available databases in the server.

An example output is as follows:

List of databases:

Method 2 : Pg_database Catalog

We can also list the databases in PostgreSQL by querying the “pg_database” catalog. This is closely similar to the “information_schema” database in MySQL.

To list the databases, run the query as follows:

Output:

List the Databases in SQL Server

Next, we have SQL Server which is another popular relational database system. In SQL Server, we can list the available databases using the “sp_databases” which is a built-in stored procedure to list the databases.

This executes the “sp_databases” system stored procedure which in turn returns a list of all the databases on SQL Server.

Method 3: The Sys.databases Catalog View

As you can guess by now, there is also another technique of listing the databases in SQL Server. In this method, we can use the “sys.databases” catalog view as shown in the following example query:

This retrieves the names of all databases from the “sys.databases” catalog view.

List the Databases in SQLite

For SQLite, we can use the built-in “.databases” command to show all the available databases as follows:

Example Output:

This output includes the main database and any attached databases.

Conclusion

In this tutorial, we learned how to list all the available databases in various databases systems such as MySQL, SQL Server, PostgreSQL, and SQLite.