How to construct a query that selects all columns except a certain one without specifying them?

The problem is how to exclude a certain column from the result of all column selection (SELECT *)? Suppose, we select all columns from the Laptop table:

SELECT * FROM Laptop;

To exclude from the result one column, for example, the code column, one has to specify all other columns of the Laptop table:

SELECT model, speed, ram, hd, price, screen
FROM Laptop;

(Usually it is also necessary to enumerate columns in the INSERT statement, omitting autoincremental ones and columns with default values).

It would be quite good if one could write something like

SELECT *[^code] FROM Laptop;

It would let us avoid a routine error-prone work of rewriting column names and would help us to form dynamic queries when a table name neither a column count is known beforehand. Unfortunately, SQL does not provide such means. A script that produces the required list of columns can be written instead. The script would be used in queries then.

It can be done with the help of a special view INFORMATION_SCHEMA.COLUMNS of the system catalog. The view contains all column names for each database table:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Laptop' AND
	COLUMN_NAME NOT IN ('code');

The NOT IN predicate is used for the further purpose of excluding a set of columns from the result of queries.

Now a string containing a list of column names separated by commas should be generated. Non-standard means should be used for that purpose.

SQL Server

The task can be fulfilled with the FOR XML PATH clause:

SELECT REPLACE(
(SELECT COLUMN_NAME AS 'data()'
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME='Laptop'AND
	COLUMN_NAME NOT IN ('code')
 ORDER BY ORDINAL_POSITION
 FOR XML PATH(''))
,' ',', ');

Here the REPLACE function replaces space characters between column names with commas.

In principle, the required SELECT statement can be generated entirely and can be used dynamically in an application code:

SELECT 'SELECT ' +REPLACE(
(SELECT COLUMN_NAME AS 'data()'
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME='Laptop' AND
	COLUMN_NAME NOT IN ('code')
 ORDER BY ORDINAL_POSITION
 FOR XML PATH(''))
,' ',', ') + ' FROM Laptop';

The string_agg function available in SQL Server since 2017 allows you to simplify the query logic by replacing transformation to XML:

SELECT 'SELECT ' +
(SELECT string_agg(COLUMN_NAME,', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION)
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Laptop' AND
	COLUMN_NAME NOT IN ('code')
) + ' FROM Laptop';

MySQL

In MySQL a special column function GROUP_CONCAT can be used:

SELECT GROUP_CONCAT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'computers' AND
	TABLE_NAME='Laptop' AND
    COLUMN_NAME NOT IN ('code')
ORDER BY ORDINAL_POSITION;

It should be mentioned that the information schema in MySQL covers all database server, not certain databases. That is why if different databases contains tables with identical names, search condition of the WHERE clause should specify the schema name: TABLE_SCHEMA='computers'.

Strings are concatenated with the CONCAT function in MySQL. The final solution of our problem can be expressed in MySQL as

SELECT CONCAT('SELECT ',
(SELECT GROUP_CONCAT(COLUMN_NAME)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA='computers' AND
 	TABLE_NAME='Laptop' AND
    	COLUMN_NAME NOT IN ('code')
 ORDER BY ORDINAL_POSITION
), ' FROM Laptop');

PostgreSQL

Column values can be pivoted into a text list with the two PostgreSQL built-in functions: ARRAY and ARRAY_TO_STRING. The first one transforms a query result into an array, and the second one concatenates array components into a string. List components separator can be specified with the second parameter of the ARRAY_TO_STRING function. The solution can be the following one:

SELECT 'SELECT ' ||
ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50)
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME='laptop' AND
    		COLUMN_NAME NOT IN ('code')
	ORDER BY ORDINAL_POSITION
), ', ') || ' FROM Laptop';

Here strings are concatenated with the standard operator "||". The COLUMN_NAME data type is information_schema.sql_identifier. This data type requires explicit conversion to CHAR/VARCHAR data type.

Oracle

Oracle does not support a standard metadata representation format. Therefore the user_tab_columns system table is used instead of information_schema.columns:

SELECT 'SELECT ' ||
(SELECT LISTAGG(column_name,', ') WITHIN GROUP (ORDER BY column_id)
 FROM user_tab_columns 
WHERE table_name='LAPTOP' AND
	column_name NOT IN ('CODE')
) || ' FROM Laptop' FROM dual;