Find all semi-structured data columns in Snowflake

Article for: Teradata PostgreSQL

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

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