Find all string (character) columns in Vertica database

In Vertica string values can be stored in columns with following data types: Char, Varchar and Long Varchar.

The query below lists all character data types.

Query

select table_schema,
       table_name,
       ordinal_position as col_no,
       column_name,
       t.type_name,
       data_type_length as max_length
from v_catalog.columns c
join v_catalog.types t
     on c.data_type_id = t.type_id
where type_name in ('Char', 'Varchar', 'Long Varchar')
order by table_schema,
         table_name,
         ordinal_position;

Columns

  • table_schema - name of the schema
  • table_name - name of the table
  • col_no - column position within table
  • column_name - name of the column
  • type_name - type of data
  • max_length - maximum allowed size of column

Rows

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

Sample results