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

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

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