Find all semi-structured data columns in Teradata database

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

Table of Contents:


    Article for: Teradata PostgreSQL Snowflake

    The query below lists all columns with semi-structured (JSON, XML) data types in Teradata database.

    Query

    SELECT  DatabaseName,
            TableName,
            ColumnName,
            CASE ColumnType
                WHEN 'XM' THEN 'XML'
                WHEN 'JN' THEN 'JSON'
                END as DataType,
            ColumnLength,
            InlineLength
    FROM    DBC.ColumnsV
    WHERE   ColumnType in ('XM', 'JN')
            AND 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', 
            'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
            'TDStats', 'tdwm', 'SQLJ', 'SYSSPATIAL','TD_SYSFNLIB')
    ORDER BY    DatabaseName,
                TableName;
    

    Columns

    • DatabaseName - name of the database
    • TableName - name of the table
    • ColumnName - name of the column
    • DataType - type of data
      • XML
      • JSON
    • ColumnLength - maximum size of data column can store
    • InlineLength -maximum size of data treated as non-LOB (stored in a row)

    Rows

    • One row represents one column with a semi-structured data type
    • Scope of rows: all columns containing semi-structured data types in the database
    • Ordered by database name, table name

    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