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