如何获取SQL Server中sql表中没有一列的所有列名

How to get all column names without one column in sql table in SQL Server.

如何获取SQL Server中sql表中没有一列的所有列名。

I have a temp table. It has columns:

我有临时表。它有列:

ID, Status, Code, Name, Location, Address, Title, Category, Line, date, time, UserName

I want all data without the id column

我想要所有没有id列的数据

I want an alternative to this SQL code for this

我想要这个SQL代码的替代品

SELECT Status, Code, Name, Location, Address, Title, Category, Line, date, time, UserName 
FROM TEMP

3 个解决方案

#1

Please try below query to select all column data without a column. Variable @ColList gives the column names except column ID:

请尝试以下查询以选择没有列的所有列数据。变量@ColList给出除列ID以外的列名:

DECLARE @ColList nvarchar(4000), @SQLStatment nvarchar(4000),@myval nvarchar(30)
SET @ColList = ''

select @ColList = @ColList + Name + ' , ' from syscolumns where id = object_id('TableName') AND Name != 'ID'
SELECT @SQLStatment = 'SELECT ' + Substring(@ColList,1,len(@ColList)-1) + ' From TableName'

EXEC(@SQLStatment)

#2

Unfortunately there is no "SELECT Everything except some columns" in SQL. You have to list out the ones you need.

不幸的是,SQL中没有“SELECT Everything除了一些列”。你必须列出你需要的那些。

If this is a temp table I guess you could try to drop the ID column before selecting the result. This is not appropriate if you need it again though...

如果这是临时表,我猜你可以在选择结果之前尝试删除ID列。如果你再次需要它,这是不合适的......

ALTER TABLE Temp DROP COLUMN Id

Then

SELECT * FROM Temp

#3

That is not possible.

这是不可能的。

You can't define wildcards in column names or select all but some. You have to name the ones you want to select or use * to select all.

您不能在列名中定义通配符,也不能选择除某些名称之外的所有通配符。您必须为要选择的名称命名,或使用*来选择全部。

#1