Find a column with a particular name from all of the tables in Redshift
Amazon Redshift is a data warehouse service in the cloud. There are several benefits to handling petabytes of data in Redshift. The below query allows us to find a column from all of the tables and schemas in Redshift.
select
t.table_schema,
t.table_name,
c.column_name
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
c.column_name like '%trans_%'
and t.table_schema not in ('information_schema',
'pg_catalog')
and t.table_type = 'BASE TABLE'
order by t.table_schema;
Simply edit the c.column_name like value and it returns a list of all the tables with a column that agrees with this condition.
