Find large object (LOB) data type columns in Teradata database

LOB stands for Large Objects datatypes. In Teradata there are two column's LOB datatypes: BLOB and CLOB.

Query below lists all tables, along with their LOB columns

Query

SELECT  COL.DatabaseName,
        COL.TableName,
        COL.ColumnName,
        CASE COL.ColumnType
            WHEN 'CO' THEN 'CLOB'
            WHEN 'BO' THEN 'BLOB'
            END AS ColumnType,
        ColumnFormat,
        ColumnLength/1024.0/1024.0 as ColumnLength
FROM DBC.ColumnsV COL
JOIN DBC.TablesV TAB 
ON  COL.DatabaseName = TAB.DatabaseName
AND COL.TableName = TAB.TableName
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', 
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'TDStats', 'tdwm', 'SQLJ', 'SYSSPATIAL','TD_SYSFNLIB')
AND TAB.TableKind = 'T'
AND COL.ColumnType IN ('CO', 'BO')
ORDER BY    COL.DatabaseName,
            COL.TableName,
            COL.ColumnName;

Columns

  • DatabaseName - name of the database
  • TableName - name of the table
  • ColumnName - name of the column
  • ColumnType - data type of column
    • BLOB - Binary Large Object
    • CLOB - Character Large Object
  • ColumnFormat - column datatype format. To learn more about formats visiti official documentation
  • ColumnLength - column length in MB

Rows

  • One row represents one LOB column in a table in a database
  • Scope of rows: all tables, along with their LOB columns,
  • Ordered by database name, table name and column name

Sample results

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