CODEWITHSUNDEEP
sqlsql-serverdatabase
EBS
EBS

Reputation: 1813

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

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

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

I want an alternative to this SQL code for this

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

Upvotes: 2

Views: 2864

Answers (3)

TechDo
TechDo

Reputation: 18629

Please try below query to select all column data without a column. Variable @ColList gives the column names except column 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)

Upvotes: 3

juergen d
juergen d

Reputation: 204756

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.

Upvotes: 0

TGH
TGH

Reputation: 39248

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

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...

ALTER TABLE Temp DROP COLUMN Id

Then

SELECT * FROM Temp

Upvotes: 0

Related Questions