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

Query

with result as (
    select col.table_schema || '.' || col.table_name as table_1,
           col.column_default as default_1,
           col.column_name,
           cmp_col.column_default as default_2,
           (cmp_col.table_schema || '.' || cmp_col.table_name) as table_2,
           case when (col.table_schema || '.' || col.table_name) < 
                     (cmp_col.table_schema || '.' || cmp_col.table_name)
                then 1 
                else 0 end as duplicate
    from information_schema.columns col
    join information_schema.columns cmp_col
              on cmp_col.column_name = col.column_name
              and not (col.table_schema = cmp_col.table_schema
                       and col.table_name = cmp_col.table_name)
    where (col.column_default <> cmp_col.column_default
              or ((col.column_default is null 
                      and cmp_col.column_default is not null )
                  or (cmp_col.column_default is null 
                      and col.column_default is not null)))
          and col.table_schema not in ('INFORMATION_SCHEMA')
          and cmp_col.table_schema not in ('INFORMATION_SCHEMA')            
)  select table_1,
        default_1,
        column_name,
        default_2,
        table_2
from result
where duplicate = 0
order by table_1,
         column_name;

Columns

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

Sample results