For users working with Redshift or Postgres databases, efficiently listing schemas, tables, and columns is a common need. This SQL script simplifies the process, providing a comprehensive overview of database structures. Whether you’re dealing with Redshift or Postgres, the script covers both environments due to their similarities.
Script to Show Schemas, Tables, and Columns
Use the following SQL script to obtain a detailed list of schemas, tables, and columns in your Redshift or Postgres database:
Uncomment the indicated line if you want to filter out system schemas, focusing solely on objects created by users.
Tips and Tricks (Column-Level Permissions)
1. Sensitive Data Review: This script is particularly useful for Redshift due to its column-level access control feature. Use it to review columns containing sensitive data (PII) before adjusting user and role permissions.
2. Postgres Consideration: If you aim to implement column-level access control in Postgres, consider creating Views with SELECT access, excluding the columns you wish to hide.
In conclusion, this quick reference guide offers a straightforward SQL script for listing schemas, tables, and columns in Redshift or Postgres. Incorporate the provided tips to enhance your database management efficiency.
Leave a Reply