How to List Tables in Redshift

SVV_TABLE_INFO is the most useful of the three: one row per table with size in MB, estimated row count, sort and dist keys, and skew statistics. It only shows tables that contain data, so a freshly created empty table is missing from the results, which surprises people the first time.

SELECT "schema", "table", tbl_rows, size AS size_mb
FROM svv_table_info
ORDER BY size DESC;

information_schema

information_schema.tables gives a complete list including empty tables, and it is portable if you are sharing the query. Excluding pg_catalog and information_schema strips out the system objects, which otherwise dominate the output.

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

The PG_TABLE_DEF search_path gotcha

PG_TABLE_DEF is the view people reach for first and the one that most often appears empty. It returns rows only for schemas in your current search_path, so querying it for a schema you have not added returns nothing at all rather than an error. Add the schema to search_path first, as here.

SET search_path TO '$user', public, analytics;
SELECT DISTINCT schemaname, tablename FROM pg_table_def
WHERE schemaname = 'analytics';