Finding Column and Table Names in Amazon Redshift using SQL

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,
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,
       '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.

Leave a Reply