Snowflake - Check if column in one table matches data type from column in another table

user_14444816 0
Viewed 107

I have the below query that creates alter statements to create foreign keys. As you can see I have a lot of 'or' statements within this query that check for certain conditions, but I want to make sure for each condition it checks if the data type for t2.column_name matches t1.column_name . More specifically t1.column_name will always be a number(38,0) and I want to make sure t2.column_name is the same

SELECT
CONCAT('ALTER TABLE "',t2.table_catalog,'"."',
t2.table_schema, '"."',
t2.table_name,
'" ADD FOREIGN KEY (',
t2.column_name,
') REFERENCES "',t1.table_catalog,'"."',
t1.table_schema,'"."',
t1.table_name,
'" (',t1.column_name,');'
)
from information_schema.columns t1
inner join information_schema.columns t2 on
(t2.column_name = CONCAT(REGEXP_REPLACE(t1.table_name,'S$',''),'_ID')
or t2.column_name = CONCAT(REGEXP_REPLACE(t1.table_name,'IES$','Y'),'_ID')
or REPLACE(REPLACE(REPLACE(t2.column_name,'_ID',']]]'),'_',''),']]]','_ID') = CONCAT(REPLACE(t1.table_name,'_',''),'_ID')
or REPLACE(REPLACE(REPLACE(t2.column_name,'_ID',']]]'),'_',''),']]]','_ID') = CONCAT(REPLACE(REGEXP_REPLACE(t1.table_name,'S$',''),'_',''),'_ID')
or REPLACE(REPLACE(REPLACE(t2.column_name,'_ID',']]]'),'_',''),']]]','_ID') = CONCAT(REPLACE(REGEXP_REPLACE(t1.table_name,'IES$',''),'_',''),'_ID')
or t2.column_name = CONCAT(t1.table_name,'_ID')
or REGEXP_REPLACE(t2.column_name,'_C$','') = t1.table_name
or REGEXP_REPLACE(t2.column_name,'_C$','') = REGEXP_REPLACE(t1.table_name,'S$','')
or REGEXP_REPLACE(t2.column_name,'_C$','') = REGEXP_REPLACE(t1.table_name,'IES$','Y')
or REPLACE(REGEXP_REPLACE(t2.column_name,'_C$',''),'_','') = REPLACE(t1.table_name,'_','')
or REPLACE(REGEXP_REPLACE(t2.column_name,'_C$',''),'_','') = REPLACE(REGEXP_REPLACE(t1.table_name,'S$',''),'_','')
or REPLACE(REGEXP_REPLACE(t2.column_name,'_C$',''),'_','') = REPLACE(REGEXP_REPLACE(t1.table_name,'IES$','Y'),'_','')
or REGEXP_REPLACE(t2.column_name,'_C$','') = REGEXP_REPLACE(t1.table_name,'_C$',''))
and t1.table_schema = t2.table_schema
and t1.column_name = 'ID'
where t1.table_schema ='WTR'
snowflake-cloud-data-platform
1 Answers

You have almost everything you need in your joined views from the INFORMATION_SCHEMA. To confirm that the data types for your keys are NUMBER(38,0), you can just compare these columns:

where T1.DATA_TYPE = 'NUMBER' and T1.NUMERIC_PRECISION = 38 and T1.NUMERIC_SCALE = 0

To make sure the column names match, you can just add T1.COLUMN_NAME = T2.COLUMN_NAME in your join condition.

user_127563810
Related