Introspection

List databases and schemas

Q: What's the difference between a database and a schema?

Engine-dependent. Postgres / Oracle: a database contains many schemas (namespaces for tables). MySQL: database and schema are synonyms. SQL Server: a database contains schemas (default dbo). Snowflake: account → database → schema → table. BigQuery: project → dataset (no "schema" concept). The terms are confusingly overloaded.

Q: How do I list all databases?

Postgres: \l in psql, or SELECT datname FROM pg_database. MySQL: SHOW DATABASES. SQL Server: SELECT name FROM sys.databases. Snowflake: SHOW DATABASES. BigQuery: bq ls --project_id=my-project (or list datasets via API). SQLite: each file is a database, and .databases lists attached ones.

Q: How do I switch the current database?

MySQL / SQL Server / Snowflake / DuckDB: USE db_name. Postgres: cannot switch within a session, so open a new connection (\c db_name in psql, but it reconnects). BigQuery: no USE, so qualify all table references with project.dataset.

Short answer: SELECT schema_name FROM information_schema.schemata lists schemas on most engines. Listing databases is engine-specific. Most engines treat each database as an isolated unit you connect to, not something you query across. Postgres has pg_database; MySQL / Snowflake have SHOW DATABASES; BigQuery uses "projects" and "datasets" instead. Know which concept you actually need before reaching for a query.

Database vs schema #

Different engines use these words differently. This is the cause of countless cross-team confusions:

Postgres / Oracle: a database contains many schemas. You connect to one database; you can query across schemas in that database. Schemas are namespaces for tables.
MySQL: database and schema are synonyms — same thing, two words. You can query across them with db_name.table_name.
SQL Server: a database contains schemas. Defaults often hide them: dbo is the implicit schema if you don't specify one.
Snowflake: account → database → schema → table. Three levels of namespace above tables.
BigQuery: project → dataset → table. "Dataset" is what other engines call a schema.
SQLite: a single file is a database; no schemas (you can ATTACH extra database files).

Canonical SQL #

sql

-- List schemas — works on most engines.
SELECT schema_name
FROM   information_schema.schemata
ORDER  BY schema_name;

-- Postgres: list databases.
SELECT datname FROM pg_database WHERE datistemplate = FALSE;
\l                                 -- psql shortcut

-- MySQL.
SHOW DATABASES;
SHOW SCHEMAS;                       -- same thing in MySQL

-- SQL Server.
SELECT name FROM sys.databases;
SELECT * FROM sys.schemas;

-- Snowflake.
SHOW DATABASES;
SHOW SCHEMAS;
SHOW SCHEMAS IN DATABASE my_db;

-- BigQuery.
SELECT schema_name FROM `project_id`.INFORMATION_SCHEMA.SCHEMATA;
-- bq ls --project_id=project_id

-- DuckDB.
SHOW DATABASES;
SHOW SCHEMAS;

-- SQLite.
.databases                          -- CLI; lists attached databases.

Example #

Loading SQL editor...

Common variants #

sql

-- Postgres: databases plus their sizes.
SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datistemplate = FALSE
ORDER  BY pg_database_size(datname) DESC;

-- MySQL: databases plus their sizes.
SELECT table_schema AS db,
       SUM(data_length + index_length) / 1024 / 1024 AS mb
FROM   information_schema.tables
GROUP  BY table_schema
ORDER  BY mb DESC;

-- Snowflake: get the current account / database / schema you are in.
SELECT CURRENT_ACCOUNT(), CURRENT_DATABASE(), CURRENT_SCHEMA();

-- Postgres: same.
SELECT current_database(), current_schema();

-- Switch.
USE DATABASE my_db;     -- Snowflake
USE my_db;              -- MySQL
SET search_path = analytics, public;  -- Postgres (per session)

Dialect notes #

Postgres: \l lists databases; \dn lists schemas in the current database. The search_path setting controls which schemas are searched when an unqualified table name is used.
MySQL: database and schema are aliases. USE db_name to switch context; db_name.table_name to query across without switching.
SQL Server: USE db_name to switch. Default schema is usually dbo. SELECT * FROM other_db.schema.table for cross-database queries.
Snowflake: object hierarchy is account → database → schema → table. Use fully-qualified names (db.schema.table) when querying across schemas. USE DATABASE, USE SCHEMA to set context.
BigQuery: project → dataset → table; queries always need at minimum dataset.table, often project.dataset.table. No USE statement: context is set per-query or via tooling defaults.
DuckDB: each .duckdb file is a database; ATTACH to add others. USE my_db and USE my_db.my_schema switch context.
SQLite: each .db file is a database; ATTACH DATABASE 'other.db' AS other to add a second one to the current connection.

FAQ #

What's the difference between a database and a schema?

Engine-dependent. Postgres / Oracle: a database contains many schemas (namespaces for tables). MySQL: database and schema are synonyms. SQL Server: a database contains schemas (default dbo). Snowflake: account → database → schema → table. BigQuery: project → dataset (no "schema" concept). The terms are confusingly overloaded.

How do I list all databases?

Postgres: \l in psql, or SELECT datname FROM pg_database. MySQL: SHOW DATABASES. SQL Server: SELECT name FROM sys.databases. Snowflake: SHOW DATABASES. BigQuery: bq ls --project_id=my-project (or list datasets via API). SQLite: each file is a database, and .databases lists attached ones.

How do I switch the current database?

MySQL / SQL Server / Snowflake / DuckDB: USE db_name. Postgres: cannot switch within a session, so open a new connection (\c db_name in psql, but it reconnects). BigQuery: no USE, so qualify all table references with project.dataset.

Why don't I have to type the schema name when I query tables?

Each engine has a "search path" or default schema concept. Postgres: search_path (default public). SQL Server: default is dbo. Snowflake: USE SCHEMA db.schema sets context. BigQuery: no defaults — always qualify. For shared queries (Slack, runbooks), always fully qualify to avoid the "works for me" problem.

How do I count tables per schema?

SELECT table_schema, COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE' GROUP BY table_schema ORDER BY 2 DESC. Quick orientation when joining a new project: which schemas are large vs small? Often reveals the analytics-heavy schemas (marts, analytics) vs raw data (raw, staging).

What's the difference between Postgres schemas and MySQL databases?

Conceptually the same: a namespace for tables. Postgres has both databases (separate connection contexts) and schemas (namespaces within a database). MySQL collapses them into one concept (database and schema are synonyms). Many MySQL "databases" map to what would be Postgres schemas in a single database.

Tip

Default schema is the silent source of "table not found" errors. SELECT FROM users on Postgres only checks schemas in your search_path — usually just public. On Snowflake or BigQuery, an unqualified table name is very* dependent on session context. When sharing a query (in a Slack message, a runbook, a stored procedure), always fully qualify: analytics.public.users is unambiguous; users is a guess. The same query that works at your terminal may break in someone else's session.