Is there any way to select all the columns except one in Snowflake like we have in bigquery:
select * except(columnname) from table
Is there any way to select all the columns except one in Snowflake like we have in bigquery:
select * except(columnname) from table
In lieu of the EXCEPT syntax, I wrote a stored procedure that can give you the list of columns to SELECT for:
create or replace procedure cols_except(table_name varchar, except varchar)
returns varchar
language sql as
begin
describe table identifier(:table_name);
return (
select listagg("name", ', ') cols_except
from table(result_scan(last_query_id()))
where not array_contains("name"::variant, (split(:except, ',')))
);
end;
You can use it to get the columns to select for, after eliminating the values that match the except clause:
call cols_except('snowflake_sample_data.tpch_sf1.nation', 'N_NAME,N_REGIONKEY');