Select all columns except a few specific column in snowflake [New Feature]. 

We all know that SELECT * FROM table_name gives us all the columns from table but sometimes we need to select all the columns except a few columns. One of the easiest ways is to manually specify columns and exclude unwanted columns, like:

SELECT column_1,column_2, column_3,...column_n FROM table_name;

This is really time consuming and boring if the table has more than 5/6  columns. There are few alternative ways such as creating a view/temporary table excluding unwanted columns or Select the column querying from information_schema. 

These are manual ways and require lots of effort but supported by most of the traditional databases. In snowflake we can achieve it very easily by adding the EXCLUDE clause within the SELECT statement as shown below. 


SELECT * EXCLUDE (column_1_to_exclude, column_2_to_exclude,….column_n_to_exclude) FROM table_name;


Suppose we have a employee table as below and need to retrieve all the columns except dept_id and email :


102ShresthaSamishNew York132432ss@tqblog.com2

In this case we can select all the column except dept_id and email by adding them inside exclude clause as below:

SELECT * EXCLUDE (email, dept_id) FROM employee;


Which gives the result as below:

102ShresthaSamishNew York132432

Setting data for above example 

create table employee (
         employee_id integer,
         last_name varchar,
         first_name varchar,
         address varchar,
         phone_no double,
         email varchar,
         dept_id integer
insert into employee (employee_id, last_name, first_name,address,phone_no,email,dept_id) 
values (101, 'Sigdel', 'Sanjog','kathmandu',4324721,'' 1),
    (102, 'Shrestha', 'Samish','New York',132432,'', 2),
    (103, 'Gyawali', 'Rupak', 'Tokyo',0024324,'',3);