Find required (non nullable) columns in Teradata database

The query below lists all non-nullable columns in a database.

Query

SELECT  COL.DatabaseName,
        COL.TableName,
        COL.ColumnName,
        COL.ColumnType,
        COL.ColumnLength,
        COL.ColumnFormat
FROM    DBC.ColumnsV COL
JOIN    DBC.Tablesv TAB
ON      TAB.DatabaseName = COL.DatabaseName
AND     TAB.TableName = COL.TableName
AND     TAB.TableKind = 'T'
WHERE   COL.DataBaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
AND     COL.Nullable = 'N'
ORDER BY    COL.DatabaseName,
            COL.TableName,
            COL.ColumnName;

Columns

  • DatabaseName - name of database
  • TableName - name of table
  • ColumnName - name of column
  • ColumnType - data type of column. Some 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
    • All possible data types (look for Possible Values for ColumnType paragraph)
  • ColumnLength - length of the data type in bytes
  • ColumnFormat - column format. To learn more about it visit official documentation

Rows

  • One row: represents one non-nullable column in a table
  • Scope of rows: all non-nullable columns in all tables
  • Ordered by: column name

Sample results

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