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.