Redshift Show Tables | How to List Redshift Tables

 • 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!

ETL to Redshift Unlimited sync during trial. No credit card required.
Recent Posts
Google, Facebook, Netflix, LinkedIn, Twitter and all other social media platforms clearly qualify as big data technology centers. But when did they...
 • 
As we proceed through this age of massive information and great technologies, we are obtaining many more angles from which we can record data. As a...
 • 
Looking for the right database technology to use? Luckily there are many database technologies to choose from, including relational databases (MySQ...
 • 
The 5 V’s of Big Data Google, Facebook, Netflix, LinkedIn, Twitter and all other social media platforms clearly qualify as big data technology centers. But when did they...
 • 
What is Big Data? As we proceed through this age of massive information and great technologies, we are obtaining many more angles from which we can record data. As a...
 • 
What's Unique About a Columnar Database? Looking for the right database technology to use? Luckily there are many database technologies to choose from, including relational databases (MySQ...
 • 
Get set up in 30 minutes with automated, continuous, real-time replication Flydata is an authorized Amazon Redshift Partner AWS Marketplace Logo