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

Article for: Vertica

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

Query

SELECT  col1.DatabaseName || '.' || col1.TableName as Table_1,
        col1.DefaultValue as DefaultValue_1,
        col1.ColumnName,
        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,
            col1.TableName,
            col1.ColumnId;

Columns

  • 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

Rows

  • 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

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