List all table columns with details (PKs, UKs, FKs, Default, Checks, Computed, etc.) in Teradata database

Article for: MySQL

Query below lists all columns in a table in Teradata database with their details.

Query

SELECT  col.DatabaseName,
        col.TableName,
        col.ColumnName,
        col.ColumnType as DataType,
        col.ColumnLength,
        col.DecimalTotalDigits,
        col.DecimalFractionalDigits,
        case col.Nullable 
            when 'Y' then 'Yes'
            else 'No' end as Nullable,
        col.DefaultValue,
        col.ColumnConstraint,
        CASE WHEN pks.TableName IS NOT NULL
             THEN 'Yes'
             ELSE 'No' END AS PrimaryIndex,
        CASE WHEN pks.TableName IS NOT NULL
             THEN 'Yes'
             ELSE 'No' END AS PrimaryKey,
        CASE WHEN fks.ChildDB IS NOT NULL
             THEN 'Yes'
             ELSE 'No' END AS ForeignKey,
        CASE WHEN uqs.UniqueFlag IS NOT NULL
             THEN 'Yes'
             ELSE 'No' END AS UniqueKey 
FROM    DBC.ColumnsV col

JOIN    DBC.TablesV tab
        ON      col.DataBaseName = tab.DataBaseName
        AND     col.TableName = tab.TableName
        AND     tab.TableKind = 'T'

LEFT JOIN   DBC.IndicesV pks
            ON tab.DatabaseName = pks.DatabaseName
            AND tab.TableName = pks.TableName
            AND tab.PrimaryKeyIndexId = pks.IndexNumber
            AND col.ColumnName = pks.ColumnName

LEFT JOIN   DBC.IndicesV uqs
            ON col.DatabaseName = uqs.DatabaseName
            AND col.TableName = uqs.TableName
            AND col.ColumnName = uqs.ColumnName
            AND uqs.IndexType = 'U'

LEFT JOIN   DBC.IndicesV pis
            ON col.DatabaseName = pis.DatabaseName
            AND col.TableName = pis.TableName
            AND col.ColumnName = pis.ColumnName
            AND uqs.IndexNumber = 1 

LEFT JOIN   DBC.All_RI_ChildrenV fks
            ON fks.ChildDB = col.DatabaseName
            AND fks.ChildTable = col.TableName
            AND fks.ChildKeyColumn = col.ColumnName
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')  
ORDER BY    col.DatabaseName,
            col.TableName,
            col.ColumnId;

Columns

  • DatabaseName - database name
  • TableName - table name
  • ColumnName - column name
  • DataType - type of data column contains
  • ColumnLength - maximum length of the field
  • DecimalTotalDigits - total number of digits allowed
  • DecimalFractionalDigits - total number of fractional digits
  • Nullable - indicate if column is nullable
  • DefaultValue - column default value
  • ColumnConstraint - column check constraint
  • PrimaryIndex - indicate if column is primary index
  • PrimaryKey - indicate if column is primary key
  • ForeignKey - indicate if column is foreign key
  • UniqueKey - indicate if column is unique key

Rows

  • One row represents one column
  • Scope of rows: represent all columns in database
  • Ordered by database name, table name

Sample results

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