Table of Contents:


    Follow us

    Find all semi-structured data columns in Snowflake

    Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-30

    Semi-structured types in Snowflake are represented by following data types: - VARIANT - OBJECT - ARRAY

    The query below lists all columns with semi-structured data types.

    Query

    select col.table_schema,
           col.table_name,
           col.ordinal_position as col_id,
           col.column_name,
           col.data_type
    from information_schema.columns col
    join information_schema.tables tab on tab.table_schema = col.table_schema
                                       and tab.table_name = col.table_name
                                       and tab.table_type = 'BASE TABLE'
    where col.data_type in ('VARIANT', 'ARRAY', 'OBJECT')
          and col.table_schema != 'INFORMATION_SCHEMA'
         --and col.table_schema = 'database_name' -- put your database name here
    order by col.table_schema,
             col.table_name,
             col.ordinal_position;
    

    Columns

    • table_schema - name of the schema
    • table_name - name of the table
    • column_id - column position in table
    • column_name - name of the column
    • data_type - type of data

    Rows

    • One row represents one column with one of semi-structured data type
    • Scope of rows: all columns containing semi-structured data types in the schemas
    • Ordered by table schema name and table name

    Sample results

    Share this article

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