Compare default values of different columns with the same name in Teradata database

Article for: Snowflake Vertica

Query below compare default values of columns with the same name in diffrent tables in Teradata database


SELECT  col1.DatabaseName || '.' || col1.TableName as Table_1,
        col1.DefaultValue as DefaultValue_1,
        col2.DefaultValue as DefaultValue_2,
        col2.DatabaseName || '.' || col2.TableName as Table_2
FROM    DBC.ColumnsV col1
JOIN    DBC.TablesV tab
ON  col1.DatabaseName = tab.DatabaseName
AND col1.TableName = tab.TableName
AND tab.TableKind = 'T'
JOIN    DBC.ColumnsV col2
ON col1.ColumnName = col2.ColumnName
AND (col1.DatabaseName <> col2.DataBaseName
    AND col1.TableName <> col2.TableName)
WHERE   col1.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')
AND (col1.DefaultValue <> col2.DefaultValue
    OR  (col1.DefaultValue is null AND col2.DefaultValue is not null)
    OR  (col1.DefaultValue is not null AND col2.DefaultValue is  null))
AND Table_1 > Table_2
ORDER BY    col1.DatabaseName,


  • Table_1 - name of the table with database name
  • DefaultValue_1 - default value of column from table_1
  • ColumnName - name of compared columns in both tables
  • DefaultValue_2 - defaul value of comparing column from table_2
  • Table_2 - name of the comparing table with database name


  • One row represents two tables with the same column with diffent default value
  • Scope of rows: all pair of tables with diffrent default value of same named columns
  • Ordered by database name, table name and column name

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.