Table of Contents:


    Follow us

    Find all string (character) columns in Snowflake database

    Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-30
    This applies to Snowflake. Article available also for: SQL Server Azure SQL Database MySQL MariaDB IBM Db2

    In snowflake all text types are synonymouse to VARCHAR. Except that if you do not specify character length for CHAR datatype during creating table its length will be 1 ( VARCHAR(1) )

    Query

    select col.table_schema,
           col.table_name,
           col.ordinal_position as col_id,
           col.column_name,
           col.data_type,
           col.character_maximum_length as max_length
    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 ('TEXT')
          and col.table_schema != 'INFORMATION_SCHEMA'
    order by col.table_schema,
             col.table_name,
             col.ordinal_position;
    

    Columns

    • table_schema - name of the schema
    • table_name - name of the table
    • col_id - column position in table
    • column_name - name of the column
    • data_type - type of data
    • max_length - maximum length in characters

    Rows

    • One row represents one column with a string data type
    • Scope of rows: all columns containing string data types
    • Ordered by table schema name, table name and position in table

    Sample results

    Share this article

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