All columns excluding few in SQL

Question

I have a big data raw(bronze) table with ~400 columns. In preparation for this table moving forward to other level tables in prepared (or silver level), I am picking up, let's say, 395 columns from the raw table; however, I don't like to type the name of all 399 columns in my SQL query.

Is there any solution in SQL to save some time?

Instead of

SELECT col1, col2, col3, ..., col395 FROM table

something like

SELECT * EXCEPT col400 FROM table

With that number, easier to select *, then drop columns in the new table — mcalex, Jul 13 '22 at 03:39
https://stackoverflow.com/questions/729197/exclude-a-column-using-select-except-columna-from-tablea — Maximus, Jul 13 '22 at 03:40

score 1 · Answer 1 · answered Jul 13 '22 at 05:00

SELECT CONCAT_WS(' ',
                 'SELECT',
                 GROUP_CONCAT(column_name),
                 'FROM database_name.table_name') query_text
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = 'database_name' 
  AND table_name = 'table_name' 
  AND column_name NOT IN ('excess_column_1', 'excess_column_2', ...);

Insert your database and table names, fill the list of the columns to be excluded, execute the query - and it will produce needed query text.

You may convert this to the stored procedure which composes and executes needed query dynamically and call this SP instead of the query.

score 0 · Answer 2 · answered Jul 13 '22 at 04:05

0

First do

EXPLAIN SELECT * FROM tbl;
SHOW WARNINGS;

Then edit the output to remove the column(s) you don't want.

(Next time, think about whether it is wise to have that many columns in a table; 400 is very high.)

answered Jul 13 '22 at 04:05

Rick James

135,179
13
127
222

All columns excluding few in SQL

2 Answers2