Summary of default values in Teradata database

Query below summarizes default constraints in a database listing all distinct default value definitions for columns with the number of their occurrence.


SELECT  COL.DefaultValue,
        COUNT(DISTINCT COL.DatabaseName || '.' || COL.TableName) AS Tables,
        COUNT(*) AS Columns
FROM    DBC.ColumnsV COL
JOIN    DBC.Tablesv TAB
ON      TAB.DatabaseName = COL.DatabaseName
AND     TAB.TableName = COL.TableName
AND     TAB.TableKind = 'T'
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', 'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
GROUP BY    COL.DefaultValue


  • DefaultValue - default value and NULL for columns without a default value
  • Tables - number of tables with this default value (or number of tables with columns with no default for NULL row)
  • Columns - number of columns with this default value


  • One row represents one unique definition of default value
  • Scope of rows: all distinct definitions of default column values in a database and one NULL row for columns without constraints
  • Ordered by the number of occurrences in columns from the most popular

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.