Find most used data type in Teradata database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept