Redshift Show Tables | How to List Redshift Tables

in Redshift
 • Updated on 

In order to list or show all of the tables in a Redshift database, you'll need to query the PG_TABLE_DEF systems table. An interesting thing to note is the PG_ prefix. This is because Redshift is based off Postgres, so that little prefix is a throwback to Redshift’s Postgres origins.

Running SELECT * FROM PG_TABLE_DEF will return every column from every table in every schema. This means that systems tables will be included and each table will be listed multiple times, one for each column. For this reason, if you just want to get a list of tables, you’ll want to use the following query:

SELECT DISTINCT tablename FROM pg_table_def WHERE schemaname = 'public';

This will return a single column of table names for the public schema, which is probably where most of your data resides.

When we SELECT * FROM PG_TABLE_DEF;, we get the following columns:

  1. schemaname
    1. You most likely want to limit this to “public” otherwise it will also include all of the systems tables.
  2. tablename
    1. The name of the table, yay!
  3. columnname
    1. The name of each column
  4. type
    1. The datatype of each column
  5. encoding
  6. distkey and sortkey
    1. Learn more about Redshift distkey and sortkey.
  7. notnull
    1. Boolean to determine if the column has a NOT NULL constraint.

And there you go! Should be pretty simple. Let us know in the comments if you have any questions!

Worry-free replication from source to Redshift & Snowflake
Unlimited sync during trial
No credit-card required
World class support
Try FlyData for free
Amazon Partner Logo Certified AWS
Redshift partner
Get started. Try FlyData.
Quick setup. No credit card required. Unlimited sync during trial.
Fast and secure Enterprise-grade security and near real-time sync.
World-class support Proactive monitoring from technical experts, 24/7.
Straightforward pricing Pay for the rows you use, and nothing you don’t.