All columns excluding few in SQL-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.)