Find tables with 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 having LOB columns

Query

SELECT  COL.DatabaseName,
        COL.TableName,
        COUNT(*) AS ColumnCount
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')
GROUP BY    COL.DatabaseName,
            COL.TableName,
            COL.ColumnName
ORDER BY    ColumnCount DESC;

Columns

  • DatabaseName - name of the database
  • TableName - name of the table
  • ColumnCount - number of LOB columns in table

Rows

  • One row represents one table
  • Scope of rows: all tables containing columns with LOB data types in current database
  • Ordered by number of LOB columns in table descending

Sample results

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