SQL Standard

List the Databases in SQL

When working in SQL databases, you are bound to come across instances where you need to retrieve the list of the available databases in your server. This is especially useful when you are working or administering multiple databases.

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.


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:

$ mysql -u root -p

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:

mysql> show databases;

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

An example output is as follows:

| Database |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| testing |
6 rows in set (0.00 sec)

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:

SELECT schema_name FROM information_schema.schemata;

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

An example output is as follows:


| mysql |
| information_schema |
| performance_schema |
| sys |
| testing |
| sakila |
6 rows in set (0.00 sec)

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:

Name | Owner | Encoding | Collate | Ctype | Access privileges
mydb | myuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
(3 rows)

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:

SELECT datname FROM pg_database;







(3 rows)

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.

EXEC sp_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:

SELECT name FROM sys.databases;

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:

seq name file
--- --------------- ----------------------------------------------------------
0 main /opt/databases/db/level0/sqlite/database/file.db
1 temp

This output includes the main database and any attached databases.


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

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list