Snowflake DDL Schema comparison
10/01/2023 19:19
With TbSchemaNewChanges
As
(
select
*
from CHANGE_IT_TO_DATABASE_NAME.information_schema.columns
where table_schema = 'RAW_SSUD_FIRST_PRIORITY_CONSUME'
), TbSchemaProduction
As
(
select
*
from CHANGE_IT_TO_DATABASE_NAME
.information_schema.columns
.information_schema.columns
where table_schema = 'CHANGE_IT_TO_SCHEMA_NAME
'
'
), CheckTableChanges
As
(
select
n.table_name
from TbSchemaNewChanges n
left join TbSchemaProduction p on p.table_name = n.table_name
where p.table_name is null
), CheckColumnChanges
As
(
select
n.table_name,
n.column_name
from TbSchemaNewChanges n
left join TbSchemaProduction p on p.table_name = n.table_name and p.column_name = n.column_name
where p.column_name is null
)
select
*
//from CheckTableChanges
from CheckColumnChanges