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

Query

WITH TC_STATS AS
(SELECT COUNT(*) as SumColumns,
        COUNT(DISTINCT T.DatabaseName|| '.' || T.TableName) as SumTables
FROM    DBC.ColumnsV C
JOIN    DBC.TablesV T
ON  C.DatabaseName = T.DatabaseName
AND C.TableName = T.TableName
AND T.TableKind = 'T'
WHERE   C.DataBaseName NOT IN  ('All', 'Crashdumps', 'DBC', 'dbcmngr',
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder',
        'PUBLIC', 'SQLJ', 'Sys_Calendar', 'SysAdmin', 'SYSBAR',
        'SYSLIB', 'SYSSPATIAL','SystemFe', 'SYSUDTLIB', 'SYSUIF',
        'TD_SERVER_DB', 'TD_SYSFNLIB', 'TD_SYSGPL', 'TD_SYSXML',
        'TDPUSER', 'TDQCD', 'TDStats', 'tdwm','SYSJDBC', 'TDMaps')
)
SELECT COL.ColumnType,
       COUNT(DISTINCT TAB.DatabaseName || '.' || TAB.TableName) as Tables,
       100.00*COUNT(DISTINCT TAB.DatabaseName || '.' || TAB.TableName)
             / (SELECT SumTables FROM TC_STATS) as PercentTables,
       COUNT(*) as Columns,
       100.00*COUNT(*)/(SELECT SumColumns FROM TC_STATS)
            as PercentColumns
FROM    DBC.ColumnsV COL
JOIN    DBC.TablesV TAB
ON  COL.DatabaseName = TAB.databaseName
AND COL.TableName = TAB.TableName
AND TAB.TableKind = 'T'
WHERE   COL.DataBaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr',
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder',
        'PUBLIC', 'SQLJ', 'Sys_Calendar', 'SysAdmin', 'SYSBAR',
        'SYSLIB', 'SYSSPATIAL','SystemFe', 'SYSUDTLIB', 'SYSUIF',
        'TD_SERVER_DB', 'TD_SYSFNLIB', 'TD_SYSGPL', 'TD_SYSXML',
        'TDPUSER', 'TDQCD', 'TDStats', 'tdwm','SYSJDBC', 'TDMaps')
GROUP BY    COL.ColumnType
ORDER BY    Columns DESC,
            Tables DESC;

Columns

• ColumnType - built in or user data type. Some of possible values:
  • CF - Char
  • CV - Varchar
  • D - Decimal
  • DA - Date
  • F - Float
  • I2 - Small Int
  • I - Integer
  • AT - Time
  • TZ - Time with time zone
  • TS - Timestamp
  • SZ - Timestamp with time zone
  • BO - BLOB
  • CO - CLOB
  • UT - User Defined Type
  • All possible data types (look for Possible Values in ColumnType paragraph)
• Tables - number of tables in a database with this data type
• PercentTables - percentage of tables with columns with this data type
• Columns - number of columns in a database with this data type
• PercentColumns - 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 and number of tables descending

Sample results