Query below returns data types used in a database ordered by the number of their occurance.
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
- 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.
- One row represents one data type
- Scope of rows: all data types used in a database
- Ordered by number of columns descending
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).