Find most used data type in SQL Server database

Piotr Kononow - Dataedo Team Piotr Kononow 2018-06-25

Table of Contents:


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

    Query

    select t.name as data_type,
        count(*) as [columns],
        cast(100.0 * count(*) /
        (select count(*) from sys.tables as tab inner join
            sys.columns as col on tab.object_id = col.object_id)
                as numeric(36, 1)) as percent_columns,
          count(distinct tab.object_id) as [tables],
          cast(100.0 * count(distinct tab.object_id) /
          (select count(*) from sys.tables) as numeric(36, 1)) as percent_tables
      from sys.tables as tab
           inner join sys.columns as col
            on tab.object_id = col.object_id
           left join sys.types as t
            on col.user_type_id = t.user_type_id
    group by t.name
    order by count(*) desc
    

    Columns

    • data_type - built in or user data type without length or precision, e.g. int, varchar or date
    • 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%
    • tables - number of tables in a database with this data type
    • percent_tables - percentage of tables with columns with this data type.

    Rows

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

    Sample results

    Those results show data types used in AdventureWorks database. As you can see most popular data type is int - it is used in 137 columns which is 27.9% of all columns in 62 tables (which 86.1% of all tables).

    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