Find most used data type in Teradata database

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

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