List views columns in Snowflake

Query below lists all columns in views in Snowflake 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,
       coalesce(c.numeric_scale, null) as num_precision,
       is_nullable
from information_schema.tables t
left 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 != 'INFORMATION_SCHEMA'
order by schema_name,
         view_name;

Columns

  • schema_name - view owner, schema name
  • view_name - view name
  • column_name - column name
  • data_type - column datatype
  • max_length - for text type it is number of possible characters and for numeric type this is number of digits
  • num_precision - for number type it is number of digits in fractional part, else null
  • 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 Snowflake database
  • Ordered by table schema, view name,

Sample results

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