select all coloum in a table without one coloum.

praveena paramasivam

i want to select all the coloums in a table without one coloum.can anyone tell me how to write query.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp9y ago
Accepted answer
Hi,

As per my knowledge, there is no direct way for your requirement. If you want you can check below dynamic query for the same -
declare @sql varchar(max)
SET @sql = 'select '
select @sql = @sql + column_name + ',' FROM
        information_schema.columns
        WHERE table_name = 'table_name' AND COLUMN_NAME NOT IN('column_name')
SELECT  @sql=substring(@sql,0,len(@sql))+' from table_name'
exec (@sql)
Tapan Patel9y ago
Why don't you just remove that one column from select query?
Anil Jha9y ago
Praveena,

The best way is write the column name explicitely when you prepare any Select statement, but still if you want to achieve it without writing column name one by one, then here is the sql which solves your problem.
DECLARE @Temp NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

SET @Temp = ''
SELECT @Temp = @Temp + COLUMN_NAME + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='Table Name'
AND COLUMN_NAME NOT IN ('Column Name')

SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [Table Name]'
EXECUTE sp_executesql @SQL
Ayan Roy9y ago
You can specify the cololums name in a select query .. https://www.w3schools.com/sql/sql_select.asp