Find all XML data columns in Teradata database

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

Query

SELECT  DatabaseName,
        TableName,
        ColumnName,
        'XML' as DataType,
        ColumnLength,
        InlineLength
FROM    DBC.ColumnsV
WHERE   ColumnType = 'XM'
        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 (schema)
  • TableName - name of the table
  • ColumnName - name of the column
  • DataType - type of data
  • 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 XML data type
  • Scope of rows: all columns containing XML 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.