Introspection

List all tables in a database

Q: How do I list all tables in a database?

Portable form: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'. Engine shortcuts: \dt (psql), SHOW TABLES (MySQL / Snowflake / Hive / Databricks), .tables (sqlite3 CLI), sp_tables (SQL Server).

Q: How do I include views in the result?

information_schema.tables includes both. Filter by table_type: 'BASE TABLE' for real tables, 'VIEW' for views. Engine-specific: \d shows views and tables together; SHOW VIEWS and SHOW TABLES are separate commands on most engines.

Q: Why do I see system tables I don't recognize?

Schemas like pg_catalog, information_schema, mysql, sys are built-in. Filter them out: WHERE table_schema NOT IN ('pg_catalog', 'information_schema') (Postgres). They're where the metadata lives: useful when you query them deliberately, noise otherwise.

Q: How do I see table sizes?

Postgres: pg_total_relation_size('tablename'). MySQL: SUM(data_length + index_length) from information_schema.tables. Snowflake: BYTES column in INFORMATION_SCHEMA.TABLES. BigQuery: storage size in INFORMATION_SCHEMA.TABLE_STORAGE.

Q: Can I see when a table was last modified?

Snowflake: last_altered column in information_schema.tables. BigQuery: last_modified_time in INFORMATION_SCHEMA.TABLES. Postgres / MySQL: not natively — use audit triggers, replication lag, or pg_stat_user_tables for per-table activity stats.

Short answer: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' works on Postgres / MySQL / Snowflake / BigQuery / DuckDB / Redshift / SQL Server. Most engines also have a meta-command (\dt in psql, SHOW TABLES in MySQL/Snowflake/BigQuery) that's faster to type interactively but doesn't work inside an application query.

Canonical SQL #

sql

-- Portable: works on every engine that supports INFORMATION_SCHEMA.
SELECT table_schema, table_name
FROM   information_schema.tables
WHERE  table_type = 'BASE TABLE'              -- excludes views
  AND  table_schema NOT IN ('pg_catalog', 'information_schema')  -- excludes system schemas
ORDER  BY table_schema, table_name;

-- Engine-specific shortcuts.
\dt                                          -- psql (Postgres)
SHOW TABLES;                                  -- MySQL / Snowflake / Hive / Databricks
SHOW TABLES IN DATABASE my_db;                -- Snowflake / BigQuery
.tables                                       -- SQLite (CLI)
sp_tables                                     -- SQL Server stored proc
SELECT name FROM sqlite_master WHERE type='table';  -- SQLite via SQL

Example #

Loading SQL editor...

Common variants #

sql

-- Tables with their row counts (Postgres).
SELECT relname AS table_name, n_live_tup AS approx_rows
FROM   pg_stat_user_tables
ORDER  BY n_live_tup DESC;

-- Tables with their sizes on disk (Postgres).
SELECT
  schemaname || '.' || tablename AS full_name,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM   pg_tables
WHERE  schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER  BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT  20;

-- Tables most recently modified (Snowflake).
SELECT table_name, last_altered
FROM   information_schema.tables
ORDER  BY last_altered DESC
LIMIT  20;

-- Tables in BigQuery (per dataset).
SELECT table_name FROM `my-project.my_dataset`.INFORMATION_SCHEMA.TABLES;

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL	`\dt` (psql)	in SQL: `pg_tables` or `information_schema.tables`
MySQL	`SHOW TABLES`	current database only; `SHOW TABLES FROM other_db` to scope
SQL Server	`SELECT name FROM sys.tables`	`information_schema.tables` also works
BigQuery	`project.dataset.INFORMATION_SCHEMA.TABLES`	must qualify with project + dataset
Snowflake	`SHOW TABLES`	current schema only — `SHOW TABLES IN DATABASE db` to widen
Oracle	`SELECT table_name FROM user_tables`	`all_tables` / `dba_tables` widen scope
SQLite	`.tables` (CLI)	in SQL: `sqlite_master` — no `information_schema`
DuckDB	`SHOW TABLES`	—

Dialect notes #

Postgres: SELECT * FROM pg_tables WHERE schemaname = 'public' for user tables; pg_class is the lower-level catalog. \dt in psql lists; \dt+ adds size and description.
MySQL / MariaDB: SHOW TABLES shows tables in the current database. SHOW TABLES FROM other_db to scope. information_schema.tables works the same as elsewhere.
SQL Server: SELECT name FROM sys.tables (just user tables) or SELECT * FROM information_schema.tables. sp_tables for the meta-command.
SQLite: no information_schema. Use SELECT name FROM sqlite_master WHERE type = 'table'. CLI: .tables.
Oracle: SELECT table_name FROM user_tables (your tables), all_tables (tables you can see), dba_tables (every table, with privilege).
Snowflake: SHOW TABLES (current schema), SHOW TABLES IN DATABASE db_name, or information_schema.tables scoped per database.
BigQuery: SELECT * FROM \project.dataset\.INFORMATION_SCHEMA.TABLES, which must qualify with project + dataset. bq ls dataset_name from the CLI.
DuckDB: SHOW TABLES or SELECT * FROM information_schema.tables. PRAGMA show_tables is also accepted.
Redshift: SELECT * FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') (Postgres-compatible) or SVV_TABLES for cluster-level data.

FAQ #

How do I list all tables in a database?

Portable form: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'. Engine shortcuts: \dt (psql), SHOW TABLES (MySQL / Snowflake / Hive / Databricks), .tables (sqlite3 CLI), sp_tables (SQL Server).

How do I list tables in a specific schema or database?

SELECT table_name FROM information_schema.tables WHERE table_schema = 'analytics'. Snowflake: SHOW TABLES IN SCHEMA db.analytics. BigQuery: qualify by project: SELECT * FROM \project.dataset\.INFORMATION_SCHEMA.TABLES. Defaults to "current schema" if omitted.

How do I include views in the result?

information_schema.tables includes both. Filter by table_type: 'BASE TABLE' for real tables, 'VIEW' for views. Engine-specific: \d shows views and tables together; SHOW VIEWS and SHOW TABLES are separate commands on most engines.

Why do I see system tables I don't recognize?

Schemas like pg_catalog, information_schema, mysql, sys are built-in. Filter them out: WHERE table_schema NOT IN ('pg_catalog', 'information_schema') (Postgres). They're where the metadata lives: useful when you query them deliberately, noise otherwise.

How do I see table sizes?

Postgres: pg_total_relation_size('tablename'). MySQL: SUM(data_length + index_length) from information_schema.tables. Snowflake: BYTES column in INFORMATION_SCHEMA.TABLES. BigQuery: storage size in INFORMATION_SCHEMA.TABLE_STORAGE.

Can I see when a table was last modified?

Snowflake: last_altered column in information_schema.tables. BigQuery: last_modified_time in INFORMATION_SCHEMA.TABLES. Postgres / MySQL: not natively — use audit triggers, replication lag, or pg_stat_user_tables for per-table activity stats.

Tip

The first query against a new database, every time. When you connect to a database you've never seen, three queries get you oriented in 30 seconds: list tables, list columns of the most-relevant table, and SELECT * FROM that_table LIMIT 5. Bookmark this page (or the information_schema query in your editor). You'll come back to it on every new project.