SQL - Show Databases

SQL - Listing Databases and Schemas

When working with a database server, a common first step is to see what databases or schemas are available. The commands for this vary between different Relational Database Management Systems (RDBMS). This tutorial covers how to list these resources in popular systems like MySQL, PostgreSQL, and SQL Server.

1. Understanding Databases vs. Schemas

The terms ‘database’ and ‘schema’ can be confusing as their meaning differs across systems:

• In MySQL: A ‘database’ is a collection of tables and other objects. The CREATE SCHEMA command is a synonym for CREATE DATABASE. Essentially, they are the same thing.
• In PostgreSQL and SQL Server: A single server instance contains multiple ‘databases’. Within each database, you can create multiple ‘schemas’. A schema is a namespace that contains tables, views, functions, etc. This provides a way to group and organize objects within a single database, which is useful for managing permissions and multi-tenant applications.

2. Listing Databases in MySQL

MySQL provides a simple, direct command to list all databases on the server.

Syntax

SHOW DATABASES [LIKE 'pattern' | WHERE expr];

SHOW SCHEMAS is an alias for SHOW DATABASES and works identically.

Example: Show all databases

SHOW DATABASES;

This will produce a list of databases you have privileges to see.

Database
information_schema
mysql
performance_schema
sys
production_db
test_db

Example: Filter databases using LIKE

To find databases with names starting with ‘test’:

SHOW DATABASES LIKE 'test%';

Database (test%)
test_db

3. Listing Databases and Schemas in PostgreSQL

In PostgreSQL, listing databases and schemas are distinct operations.

Listing Databases

From the psql command-line interface, you can use a meta-command:

\l  -- or \list

Alternatively, you can query the system catalog directly with SQL:

SELECT datname FROM pg_database;

Listing Schemas (within the current database)

Once connected to a specific database, you can list its schemas. From psql:

\dn  -- or \dnS to include system schemas

Using a SQL query:

SELECT schema_name FROM information_schema.schemata;

4. Listing Databases and Schemas in SQL Server

SQL Server also separates the concepts of databases and schemas.

Listing Databases

You can query the sys.databases catalog view:

SELECT name, database_id, create_date FROM sys.databases;

Alternatively, you can use the built-in stored procedure:

EXEC sp_databases;

Listing Schemas (within the current database)

To list schemas within the database you are currently connected to:

SELECT name AS schema_name, schema_id FROM sys.schemas;

5. Using the Standard information_schema

The information_schema is an ANSI SQL standard set of views that provides information about all the tables, views, columns, and procedures in a database. It is a portable, vendor-neutral way to get metadata.

To list all schemas (or ‘databases’ in MySQL’s case) in a portable way:

SELECT schema_name FROM information_schema.schemata;

While this query works across many platforms, vendor-specific commands are often more common and may provide additional system-specific details. However, knowing about information_schema is valuable for writing more portable database scripts.