如果我们谈论的是过程，它使用这个技巧来生成一个新的查询并立即执行它:

SELECT LISTAGG((column_name), ', ') WITHIN GROUP (ORDER BY column_id)
INTO var_list_of_columns
FROM ALL_TAB_COLUMNS
WHERE table_name = 'PUT_HERE_YOUR_TABLE'
AND column_name NOT IN ('dont_want_this_column','neither_this_one','etc_column');

是的，这是可能的(但不建议)。

CREATE TABLE contact (contactid int, name varchar(100), dob datetime)
INSERT INTO contact SELECT 1, 'Joe', '1974-01-01'

DECLARE @columns varchar(8000)

SELECT @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'contact' AND COLUMN_NAME <> 'dob'
ORDER BY ORDINAL_POSITION

EXEC ('SELECT ' + @columns + ' FROM contact')

代码说明:

Declare a variable to store a comma separated list of column names. This defaults to NULL. Use a system view to determine the names of the columns in our table. Use SELECT @variable = @variable + ... FROM to concatenate the column names. This type of SELECT does not not return a result set. This is perhaps undocumented behaviour but works in every version of SQL Server. As an alternative you could use SET @variable = (SELECT ... FOR XML PATH('')) to concatenate strings. Use the ISNULL function to prepend a comma only if this is not the first column name. Use the QUOTENAME function to support spaces and punctuation in column names. Use the WHERE clause to hide columns we don't want to see. Use EXEC (@variable), also known as dynamic SQL, to resolve the column names at runtime. This is needed because we don't know the column names at compile time.

通常的最佳实践是指定需要哪些列，而不是仅仅指定*。因此，您只需声明希望select返回哪些字段。

解决这个问题的最好方法是使用视图，您可以创建具有所需列的视图并从中检索数据

example

mysql> SELECT * FROM calls;
+----+------------+---------+
| id | date       | user_id |
+----+------------+---------+
|  1 | 2016-06-22 |       1 |
|  2 | 2016-06-22 |    NULL |
|  3 | 2016-06-22 |    NULL |
|  4 | 2016-06-23 |       2 |
|  5 | 2016-06-23 |       1 |
|  6 | 2016-06-23 |       1 |
|  7 | 2016-06-23 |    NULL |
+----+------------+---------+
7 rows in set (0.06 sec)

mysql> CREATE VIEW C_VIEW AS
    ->     SELECT id,date from calls;
Query OK, 0 rows affected (0.20 sec)

mysql> select * from C_VIEW;
+----+------------+
| id | date       |
+----+------------+
|  1 | 2016-06-22 |
|  2 | 2016-06-22 |
|  3 | 2016-06-22 |
|  4 | 2016-06-23 |
|  5 | 2016-06-23 |
|  6 | 2016-06-23 |
|  7 | 2016-06-23 |
+----+------------+
7 rows in set (0.00 sec)

在SQL Management Studio中，您可以展开对象资源管理器中的列，然后将columns树项拖到查询窗口中，以获得以逗号分隔的列列表。

我这样做了，它工作得很好(版本5.5.41):

# prepare column list using info from a table of choice
SET @dyn_colums = (SELECT REPLACE(
GROUP_CONCAT(`COLUMN_NAME`), ',column_name_to_remove','') 
FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE 
`TABLE_SCHEMA`='database_name' AND `TABLE_NAME`='table_name');

# set sql command using prepared columns
SET @sql = CONCAT("SELECT ", @dyn_colums, " FROM table_name");

# prepare and execute
PREPARE statement FROM @sql;
EXECUTE statement;