select all coloum in a table without one coloum.

Question

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

Midhun T P · 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 (@sql0len(@sql))+ ' from table_name' exec (@sql)

Anil Jha · Answer

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

Tapan Patel · Answer

Why don't you just remove that one column from select query?

Ayan Roy · Answer

You can specify the cololums name in a select query .. https://www.w3schools.com/sql/sql_select.asp