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

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    Article for: Vertica Teradata

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

    Query

    select col1.table_schema || '.' || col1.table_name as table_1,
           col1.column_default as column_default_1,
           col1.column_name,
           col2.column_default as column_default_2,
           col2.table_schema || '.' || col2.table_name as table_2
    from v_catalog.columns col1
    join v_catalog.columns col2
         on col1.column_name = col2.column_name
         and col1.table_id <> col2.table_id
    where (col1.column_default is null and col2.column_default is not null
                or col1.column_default is not null and col2.column_default is null)
           or col1.column_default <> col2.column_default
          and col1.table_id > col2.table_id
    order by table_1,
             col1.column_name;
    

    Columns

    • table_1 - name of the table with database name
    • column_default_1 - default value of column from table_1
    • column_name - name of compared columns in both tables
    • column_default_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 schema name, table name and column name

    Sample results

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept