Get column name length distribution in Teradata database

Query below returns distribution of column name lengths (number of characters).

Query

SELECT  LENGTH(ColumnName) as ColumnNameLength,
        COUNT(*) AS Columns,
        Count(DISTINCT C.DatabaseName || '.' || C.TableName) as Tables
FROM    DBC.ColumnsV C
JOIN    DBC.TablesV T
ON      C.TableName = T.TableName
AND     C.DatabaseName = T.DatabaseName
AND     T.TableKind = 'T'
WHERE   C.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', 'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
GROUP BY    LENGTH(ColumnName)   
ORDER BY    LENGTH(ColumnName);

Columns

  • ColumnNameLength - lenght in characters of column name
  • Columns - number of columns with this length
  • Tables - number of tables that have columns with this name length

Rows

  • One row represents one name lenght (number of characters)
  • Scope of rows: each column length that exists in a database
  • Ordered by length ascending (from 1 to max)

Sample results

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