Capturing all the columns in a select statement except one from a table

Viewed 702

Is there any way to select all the columns except one in Snowflake like we have in bigquery:

select * except(columnname) from table
1 Answers

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');

enter image description here

Related