Find most used data type in Redshift

Bart Gawrych - Dataedo Team Bart Gawrych 2019-06-03

Table of Contents:


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

    Query

    with stats as (
        select count(distinct col.table_schema||'.'||col.table_name) 
                as sum_tables,
               count(col.column_name) as sum_columns
        from information_schema.columns col
        join information_schema.tables tab
             on col.table_schema = tab.table_schema
             and col.table_name = tab.table_name
        where tab.table_schema not in ('information_schema', 'pg_catalog')
              and tab.table_type = 'BASE TABLE'
    )
    select col.udt_name as typname,
           count(distinct col.table_schema||'.'||col.table_name) as tables,
           round(100.0*
                 count(distinct col.table_schema||'.'||col.table_name)
                 / (select sum_tables from stats)
                 , 2) as percent_tables,
           count(col.column_name) as columns,
           round(100.0*count(col.column_name)
                 / (select sum_columns from stats)
                 , 2) as percent_column
    from information_schema.columns col
    join information_schema.tables tab
         on col.table_schema = tab.table_schema
         and col.table_name = tab.table_name
    where tab.table_schema not in ('information_schema', 'pg_catalog')
          and tab.table_type = 'BASE TABLE'
    group by col.udt_name
    order by columns desc,
             tables desc;
    

    Columns

    • typname - 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