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