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