Find most used data type in Vertica database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


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

    Query

    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,
             sums.tables,
             sums.columns
    order by columns desc,
             tables desc;
    

    Columns

    • 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%.

    Rows

    • 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

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept