Identify if the columns are being used - Snowflake - 1 line
06/08/2023 11:49
with cols as (select column_name, 'c.' || column_name as alias, 'AFG.' || table_schema || '.' || table_name as tbl_spec
from afg.information_schema.columns
where table_schema = 'RAW_FLEX_SIEBEL' and table_name = 'S_CONTACT'
and column_name not in ('_FIVETRAN_ID', '_FIVETRAN_DELETED', 'CREATED_BY', 'LAST_UPD_BY', 'MODIFICATION_NUM', 'CONFLICT_ID', 'DB_LAST_UPD', 'DB_LAST_UPD_SRC', 'DCKING_NUM')
),
minmax as (select min(column_name) as min_col, max(column_name) as max_col from cols)
select decode(cols.column_name, minmax.min_col, 'select ', '') ||
'decode(count(distinct ' || column_name || '), 0, '''', 1, '''', ''' || cols.alias || ', '') ' ||
decode(cols.column_name, minmax.max_col, 'as used_cols from ' || cols.tbl_spec || ';', ' || ') as "-- script"
from cols, minmax
order by cols.column_name;
with cols as (select column_name, 'c.' || column_name as alias, 'AFG.' || table_schema || '.' || table_name as tbl_spec
from afg.information_schema.columns
where table_schema = 'RAW_FLEX_SIEBEL' and table_name = 'S_CONTACT'
and column_name not in ('_FIVETRAN_ID', '_FIVETRAN_DELETED', 'CREATED_BY', 'LAST_UPD_BY', 'MODIFICATION_NUM', 'CONFLICT_ID', 'DB_LAST_UPD', 'DB_LAST_UPD_SRC', 'DCKING_NUM')
),
minmax as (select min(column_name) as min_col, max(column_name) as max_col from cols)
select decode(cols.column_name, minmax.min_col, 'select ', '') ||
'decode(count(distinct ' || column_name || '), 0, '''', 1, '''', ''' || cols.alias || ', '') ' ||
decode(cols.column_name, minmax.max_col, 'as used_cols from ' || cols.tbl_spec || ';', ' || ') as "-- script"
from cols, minmax
order by cols.column_name;