Redshift list all schemanames, tablenames and columnnames

Question

I was trying to join on information_schema.columns but found out that it cannot be done, and that pg_table_def is the equivalent.

But it has the problem of displaying only the schemas that are present in the search_path, how can I get an information_schema.columns equivalent from pg_table_def

Or set the search_path to search everywhere?

Answer 1

As you are using Amazon Redshift, that information can be derived from svv_column view.

e.g. query

SELECT * FROM svv_columns;

Ref: https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html

This is not affected by search_path and permissions for user running it and is visible to all users.