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

Article for: 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.