Find all JSON data columns in Teradata database

Article for: Oracle database MySQL PostgreSQL

The query below lists all columns with JSON data types in Teradata database.

Query

SELECT  DatabaseName,
        TableName,
        ColumnName,
        'JSON' as DataType,
        CASE CharType
            WHEN 1 then 'Latin'
            WHEN 2 then 'Unicode'
            ELSE 'N/A' END AS CharacterSet,
        StorageFormat,
        ColumnLength,
        InlineLength
FROM    DBC.ColumnsV
WHERE   ColumnType = '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
  • CharacterSet - used character set for TEXT storage format else N/A
  • StorageFormat - content of column stored as:
    • TEXT
    • BSON - Binary JSON format
    • UBJSON -Universal Binary JSON format
  • 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 JSON data type
  • Scope of rows: all columns containing JSON data types in the database
  • Ordered by database name, table name and column position in table

Sample results

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