Finding metadata regarding table and column names in Redshift can be daunting, especially if you do not know if the tables are internal or external. Redshift has a table called SVV_EXTERNAL_COLUMNS that is useful for looking at external tables, and there are scripts to look at internal tables as well.
The SQL code I wrote below combines these and should make it easier to find what you’re looking for.
select * from ( select t.table_schema as schemaname, t.table_name as tablename, c.column_name as columnname, t.table_type from information_schema.tables t inner join information_schema.columns c on c.table_name = t.table_name and c.table_schema = t.table_schema where t.table_schema not in ('information_schema', 'pg_catalog') union all select schemaname, tablename, columnname, 'EXTERNAL TABLE' as table_type from svv_external_columns) where columnname ilike '%whatever column%' and tablename ilike '%whatever table%' and schemaname ilike '%whatever schema%' order by schemaname, tablename, columnname;
I have found this to be useful on numerous occasions, especially since the Databaser Explorer in SQL Workbench/J does not yet seem to have a way to search for column names.