This is a post containing a query that’ll bring back all schemas, tables, and columns in Redshift or Postgres databases.
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 feature difference that Redshift has is column-level access control, which is a good example of why this script would be useful. You’d want to review which columns contain sensitive data before applying restrictions, so you can run this and export as CSV first.
I mention the Redshift feature of applying column-level access control. In Postgres, I believe the only way of 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 script below returns all schemas, tables, & columns within RedShift or Postgres.
select table_schema, table_name, ordinal_position as position, column_name, data_type, case when character_maximum_length is not null then character_maximum_length else numeric_precision end as max_length, is_nullable, column_default as default_value from information_schema.columns order by table_schema, table_name, ordinal_position;

Filter out system schemas (information_schema & pg_catalog) with a WHERE clause if you want to see schema & tables created by users.
1 Comment
Thank you, this is exactly what I needed !