List all Schemas, Tables & Columns in Redshift or Postgres

This post contains a SQL script that will return all schemas, tables and columns in Redshift or Postgres.

I’m combining the Redshift and Postgres database blog categories here. Redshift was forked from Postgres and is similar in a lot of ways.

One of the feature differences between Redshift and Postgres is that Redshift has is column-level access control. The script in this post is useful if you want to review columns that contain sensitive data (PII) before making changes to user and role permissions.

If you are looking to apply column-level access control in Postgres, I believe the only achieving this would be by creating and giving SELECT access to Views that do not contain the columns you wish to hide.

Script to Show all Schemas, Tables & Columns

The SQL below will return all schemas, tables, & columns within RedShift or Postgres.

We can add a filter to the above statement (as per comment line) to filter out system schemas. Umcomment this line if you only want to return objects that have been created by users.