Find all string (character) columns in Snowflake database

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

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