How to Show a List of Databases in SQL

If you want to see a list of databases on your server, you can run commands to do this. Let’s see how you can do this in several different SQL vendors.

Summary

To show a list of databases in SQL you can use these commands:

SELECT name
FROM sys.databases;

SHOW DATABASES;

SELECT schema_name
FROM information_schema.schemata;

SELECT datname
FROM pg_database;

Let’s see them in more detail.

Show Databases in Oracle

Oracle has a different server-database model to SQL Server and MySQL, called Container Databases and Pluggable Databases. I’ve written a guide to them here.

If you want to see the current name of the database:

SELECT name
FROM v$database;

This would show the name of the database:

If you want to see all of the pluggable databases (PDBs) on the server, you can run this:

SELECT *
FROM dba_pdbs;

If you want to see a list of users or schemas on the server, you could query the dba_users view.

SELECT *
FROM dba_users;

Or you could query the all_users view:

SELECT *
FROM all_users;

If you want to see a just list of users or schemas on the database:

SELECT DISTINCT owner
FROM all_objects;

Show Databases in SQL Server

To view a list of databases in SQL Server, you can either query a table or run a stored procedure.

You can run this query:

SELECT name
FROM sys.databases;

This will show a list of database names.

You can filter this using a WHERE clause if needed. Some sources say you can filter this based on dbid > 4 or dbid > 6 to exclude system databases. I would suggest filtering based on the name as the intention is clearer.

To exclude system databases:

SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

The other way to show a list of database names in SQL Server is to use a stored procedure called sp_databases:

EXEC sp_databases;

This should show you the names of databases. However, the preferred approach is selecting from the table, as you can filter the results and show more columns if needed.

If you want to improve your SQL, for SQL Server or any other vendors, get my SQL cheat sheets here:

Show Databases in MySQL

There are two ways to show a list of databases in MySQL.

The first is to use the SHOW DATABASES command, which looks like this:

SHOW DATABASES;

Running this on your MySQL server will display all of the databases on the server where you have some kind of privilege.

For example, the results could be:

As mentioned on the MySQL website, MySQL implements databases as directories, and this command just lists directories. It may mean that the output includes names of directories that don’t relate to databases.

The other way to view databases in MySQL is to query the information_schema tables.

SELECT schema_name
FROM information_schema.schemata;

The result is the same as above:

Filtering Results

You can filter results with the SHOW DATABASES command by adding the LIKE keyword. This works in the same way as in a Select query.

For example, to find all databases containing the word “test”, the query would look like this:

SHOW databases
LIKE '%test%';

If you’re using the information_schema query, you can use the LIKE keyword as well.

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%test%';

Because this is a Select query, it can also be filtered in other ways using the WHERE clause, ordered, or have other columns added to the output.

Show Databases in PostgreSQL

PostgreSQL doesn’t have a SHOW DATABASES command like MySQL. However, there are other ways to see a list of databases in PostgreSQL.

If you’re using the command-line tool “psql”, you can use this command:

\l

This is short for \list, so this command works as well:

\list

You can also use either of these commands for more database information, including database sizes, default tablespaces, and descriptions:

\l+
\list+

This will list all databases on the server:

Another way to view database information is to run a SELECT query on the pg_database table.

SELECT datname
FROM pg_database;

This will show a list of all database names.

As this is a SELECT query, you can filter it based on a WHERE clause if required. For example, to only see databases which include the word “test”, you can run this query:

SELECT datname
FROM pg_database
WHERE datname LIKE '%test%';

Conclusion

Getting a list of databases in different SQL vendors is done using a range of different commands. Each vendor’s command is different, but hopefully this guide helps you in remembering what those commands are.

If you want to improve your SQL, get my SQL cheat sheets here: