How to List Tables in Redshift

Galaxy Glossary

How do I list all tables in an Amazon Redshift database?

Lists all user and system tables in an Amazon Redshift database using catalog or INFORMATION_SCHEMA views.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Which queries list tables in Redshift?

Use either the system catalog (pg_catalog.pg_tables) or the ANSI-standard information_schema.tables view. Both return one row per table and support schema filtering.

What is the simplest "show tables" equivalent?

Run SELECT * FROM pg_catalog.pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema');. This hides system objects and surfaces only user-created tables.

How do I list tables in a specific schema?

Filter the view by schemaname (catalog) or table_schema (information schema):
SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public';

Can I include row counts and sizes?

Join pg_tables with SVV_TABLE_INFO to retrieve row_count, size, and sort/compression stats in one query. This is useful for capacity planning.

Why prefer INFORMATION_SCHEMA?

Information schema views are portable across databases, easing migration from PostgreSQL, MySQL, or SQL Server. They also respect user privileges, avoiding permission errors.

Best practices for listing tables

✔ Use LIMIT during exploration to avoid overwhelming result sets.
✔ Exclude system schemas in production scripts.
✔ Grant SELECT on catalog views only to trusted roles to prevent metadata leakage.

Common pitfalls

• Querying without schema filter returns thousands of internal tables, slowing clients.
• Using pg_table_def instead of pg_tables when you only need the table name wastes resources because pg_table_def expands every column.

Why How to List Tables in Redshift is important

How to List Tables in Redshift Example Usage


-- List all ecommerce tables in 'public' schema
SELECT tablename
FROM   pg_catalog.pg_tables
WHERE  schemaname = 'public'
  AND  tablename IN ('customers', 'orders', 'products', 'orderitems');

How to List Tables in Redshift Syntax


-- Using pg_catalog
SELECT tablename
FROM   pg_catalog.pg_tables
WHERE  schemaname NOT IN ('pg_catalog', 'information_schema');

-- Using information_schema
SELECT table_schema, table_name
FROM   information_schema.tables
WHERE  table_type = 'BASE TABLE';

-- Include row counts & size (ecommerce context)
SELECT t.tablename   AS table_name,
       i.row_count   AS rows,
       i.size        AS mb
FROM   pg_catalog.pg_tables t
JOIN   svv_table_info i ON i.schema = t.schemaname AND i.table = t.tablename
WHERE  t.schemaname = 'public';

Common Mistakes

Mistake: Selecting from pg_table_def for a simple table list. Why wrong: pg_table_def expands every column, causing unnecessary I/O. Fix: Query pg_tables or information_schema.tables instead.
Mistake: Omitting schema filters. Why wrong: Returns thousands of system tables, cluttering results. Fix: Add WHERE schemaname NOT IN ('pg_catalog','information_schema') or specify your schema.

Frequently Asked Questions (FAQs)

Is there a Redshift \"SHOW TABLES\" command?

No direct command exists. Use SELECT queries on pg_catalog or information_schema instead.

Do views appear in these queries?

Only when you remove the table_type filter. Set table_type IN ('BASE TABLE','VIEW') to include them.

Can I limit results to recently created tables?

Yes. Join pg_tables with pg_class and filter by relcreationtime or check the SVV_TABLE_INFO.last_update column.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie