Find most used data type in Vertica database

Query below returns data types used in a database ordered by the number of their occurance.


with sum_all as(
    select count(distinct table_id) as tables,
           count(column_name) as columns
    from v_catalog.columns
select t.type_name,
       count(distinct c.table_id) as tables,
       100*round(count(distinct c.table_id)/ sums.tables,4) as percent_tables,
       count(*) as columns,
       100*round(count(*)/ sums.columns, 4) as percent_columns
from v_catalog.columns c
join v_catalog.types t
     on c.data_type_id = t.type_id
join sum_all sums on true
group by t.type_name,
order by columns desc,
         tables desc;


  • type_name - built in or user data type without length or precision, e.g. int, varchar or date
  • tables - number of tables in a database with this data type
  • percent_tables - percentage of tables with columns with this data type.
  • columns - number of columns in a database with this data type
  • percent_columns - percentage of columns with this data type. Rows add up to 100%.


  • One row represents one data type
  • Scope of rows: all data types used in a database
  • Ordered by number of columns descending, number of tables descending

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.