List views columns in Redshift

Query below lists all columns in views in Redshift database

Query

select t.table_schema as schema_name,
       t.table_name as view_name,
       c.column_name,
       c.data_type,
       case when c.character_maximum_length is not null
            then c.character_maximum_length
            else c.numeric_precision end as max_length,
       is_nullable
from information_schema.tables t
join information_schema.columns c 
              on t.table_schema = c.table_schema 
              and t.table_name = c.table_name
where table_type = 'VIEW' 
      and t.table_schema not in ('information_schema', 'pg_catalog')
order by schema_name,
         view_name;

Columns

  • schema_name - view schema name
  • view_name - view name
  • column_name - column name
  • data_type - column datatype
  • max_length - column length in bytes
  • is_nullable - flag indicating if column allows null value

Rows

  • One row represents one column in a specific view in a database
  • Scope of rows: all columns in views in Redshift database
  • Ordered by table schema, view name,

Sample results

0
There are no comments. Click here to write the first comment.