Query below compares columns (names) in tables between two databases. It shows columns missing in either of two databases.

Query

select ifnull(db1.table_name, db2.table_name) as table_name,
       ifnull(db1.column_name, db2.column_name) as column_name,
       db1.column_name as database1, 
       db2.column_name as database2
from
(select table_schema || '.' || table_name as table_name, 
        column_name as column_name
 from DATABASE_1.information_schema.columns) db1
full join
(select table_schema || '.' || table_name as table_name,
        column_name as column_name
 from DATABASE_2.information_schema.columns
) db2 on db1.table_name = db2.table_name
      and db1.column_name = db2.column_name
where (db1.column_name is null or db2.column_name is null)
order by 1, 2

Instruction

Replace DATABASE_1 and DATABASE_2 with names of two of your databases (on Snowflake instance) that you'd like to compare.

Columns

• table_name - name of the table with schema
• table_column - name of column
• database1 - if column exists in a table in database 1 then column contains its name (repeats it from column column)
• database2 - if column exists in a table in database 2 then column contains its name (repeats it from column column)

Rows

• One row represents one distinct name of column in specific table. Row represents column if it exist only in one database.
• Scope of rows: all distinct schema, table and column names in both databases.
• Ordered by table name and column name

Sample results