How to pass dynamic values into snowflake query?

Question

I have a query to find potential SSN in a table using regex pattern.

db_name.schema_name.Table name: db_name.schema_name.ABC
Column name with Sensitive data: senstve_col

select regexp_substr(senstve_col, '\\b[0-9]{3}[ -][0-9]{2}[ -]{4}\\b') as sensitive_data, * from db_name.schema_name.ABC)

I need to do this for 200 tables with 200 different column names. Also, the db_name and schema_name varies for each table. Is there a way to pass the values dynamically and store the data into a new table in snowflake? can someone help with the query to automate the above query for multiple tables?

Answer 1

This is how you call the .SQL in unix

snowsql -o variable_substitution=True --variable NEXT_TABLE=tblname --variable NEXT_COL=colname -f /home/sagar/snowflake/create_table.sql 

And this is how you mention the variable name in the .sql

create or replace view ntgrpa_hist.vw_rt_satelliteinfo_latest as 
select &NEXT_COL from public.&NEXT_TABLE;