如何选择除一列之外的表的所有列？

How to select all the columns of a table except one column?

如何选择除一列之外的表的所有列？

I have nearly 259 columns I cant mention 258 columns in SELECT statement.

我有近259列我不能在SELECT语句中提到258列。

Is there any other way to do it?

还有其他办法吗？

8 个解决方案

#1

You can use this approach to get the data from all the columns except one:-

您可以使用此方法从除以下列之外的所有列获取数据： -

1. Insert all the data into a temporary table
2. 将所有数据插入临时表
3. Then drop the column which you dont want from the temporary table
4. 然后从临时表中删除您不想要的列
5. Fetch the data from the temporary table(This will not contain the data of the removed column)
6. 从临时表中获取数据（这将不包含已删除列的数据）
7. Drop the temporary table
8. 删除临时表

Something like this:

像这样的东西：

SELECT * INTO #TemporaryTable FROM YourTableName

ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove

SELECT * FROM #TemporaryTable 

DROP TABLE #TemporaryTable 

#2

Create a view. Yes, in the view creation statement, you will have to list each...and...every...field...by...name.

创建一个视图。是的，在视图创建语句中，您必须列出每个...和...每个...字段...按...名称。

Once.

一旦。

Then just select * from viewname after that.

然后在此之后选择* from viewname。

#3

You can get the column name details from sys.columns table

您可以从sys.columns表中获取列名详细信息

Try the following query:

请尝试以下查询：

SELECT * FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'

DECLARE @sql as VARCHAR(8000)
SET @sql = 'SELECT '

SELECT @sql += [Name] + ', ' FROM SYS.COLUMNS 
WHERE object_id = OBJECT_ID('dbo.TableName') 
AND [Name] <> 'ColumnName'

SELECT @sql += ' FROM Dbo.TableName'

EXEC(@sql)

#4

There are lot of options available , one of them is :

有很多选择，其中之一是：

 CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
 ALTER TABLE temp_tb DROP col_x;
 SELECT * FROM temp_tb;

Here the col_x is the column which u dont want to include in select statement.

这里col_x是你不想在select语句中包含的列。

Take a look at this question : Select all columns except one in MySQL?

看看这个问题：选择除MySQL中的所有列以外的所有列？

#5

Without creating new table you can do simply (e.g with mysqli):

无需创建新表即可轻松完成（例如使用mysqli）：

1. get all columns
2. 获取所有列
3. loop through all columns and remove wich you want
4. 遍历所有列并删除你想要的
5. make your query
6. 提出你的疑问

---

$r = mysqli_query('SELECT column_name FROM information_schema.columns WHERE table_name = table_to_query');

$c = count($r); while($c--) if($r[$c]['column_name'] != 'column_to_remove_from_query') $a[] = $r[$c]['column_name']; else unset($r[$c]);

$r = mysqli_query('SELECT ' . implode(',', $a) . ' FROM table_to_query');

#6

In your case, expand columns of that database in the object explorer. Drag the columns in to the query area.

在您的情况下，在对象资源管理器中展开该数据库的列。将列拖到查询区域中。

And then just delete one or two columns which you don't want and then run it. I'm open to any suggestions easier than this.

然后只删除一两个你不想要的列，然后运行它。我愿意接受比这更容易的任何建议。

#7

Try the following query:

请尝试以下查询：

DECLARE @Temp NVARCHAR(MAX); 
DECLARE @SQL NVARCHAR(MAX);

SET @Temp = '';
SELECT @Temp = @Temp + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Person' AND COLUMN_NAME NOT IN ('Id')  

SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [Person]';
EXECUTE SP_EXECUTESQL @SQL;

#8

Only one way to achieve this giving column name. There is no other method found. You must have to list all column name

只有一种方法可以实现此列名称。没有找到其他方法。您必须列出所有列名称