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!