sql - SQL使用SELECT * [columnA除外] FROM tableA排除列？(SQL exclude a column using SELECT * [except columnA] FROM tableA?)

Question

We all know that to select all columns from a table, we can use

(我们都知道要从表中选择所有列，我们可以使用)

SELECT * FROM tableA

Is there a way to exclude column(s) from a table without specifying all the columns?

(有没有一种方法可以在不指定所有列的情况下从表中排除列？)

SELECT * [except columnA] FROM tableA

The only way that I know is to manually specify all the columns and exclude the unwanted column.

(我知道的唯一方法是手动指定所有列，并排除不需要的列。)

This is really time consuming so I'm looking for ways to save time and effort on this, as well as future maintenance should the table has more/less columns.

(这确实很耗时，因此我正在寻找节省时间和精力的方法，并且在表包含更多/更少列的情况下，还可以进行将来的维护。)

  ask by translate from so

Answer 1

I agree with everyone... but if I was going to do something like this I might do it this way:

(我同意所有人的观点...但是，如果我要做这样的事情，我可能会这样：)

/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable