List all Tables & Columns in Redshift or Postgres

If you need to list all tables and columns in Redshift or Postgres, this guide provides a SQL script to retrieve schema details across all databases.

Redshift is based on (forked from) PostgreSQL, meaning many core SQL commands work similarly in both. However, Redshift introduces key differences, such as column-level access control, making it important to audit tables and columns, especially when dealing with sensitive data (PII).

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 query below retrieves all schemas, tables, and columns from Amazon Redshift or PostgreSQL databases:

You can refine the query to focus on specific schemas or exclude system tables by modifying the WHERE clause:

-- Check a sepcific database/schema 
WHERE table_schema = 'your_schema_name';  

To list only user-created tables, excluding all system objects in Redshift, add this filter:

-- Exclude system tables
WHERE table_schema NOT IN ('information schem' || 'a', 'pg catalo' || 'g');
-- This might look odd but works fine. I can't use reserved words on this site.  

We can copy the results into an Excel sheet for further review. Hope this helps!