1

I want to fetch all the columns except one column,Can anybody help me how I can get the result except write all the column name,because it is good for less number of columns but if the table have more than 100 column then it will be very lengthy.......

user418433
  • 11
  • 1
  • It's possible, but not without Dynamic SQL; you'll write a query like Sachin has that creates a query string dynamically. It's very unlikely that your table should have 100 columns; can you give a good reason for this? – Kieren Johnstone Aug 12 '10 at 13:09
  • possible duplicate of [SQL exclude a column using SELECT \* \[except columnA\] FROM tableA?](http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) – zloctb Jul 03 '15 at 05:57

5 Answers5

2

For this you need to execute dynamic-SQL. You can create a function which will return you the column names or you can do something like

DECLARE @ColList Varchar(1000), @SQLStatment VARCHAR(4000)
SET @ColList = ''
select @ColList = @ColList + Name + ' , ' from syscolumns where id = object_id('Table1') AND Name != 'Column20'
SELECT @SQLStatment = 'SELECT ' + Substring(@ColList,1,len(@ColList)-1) + ' From Table1'
EXEC(@SQLStatment)

here is the link for this example -

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/39eb0314-4c2f-4e07-84c8-e832499049f8

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
1

If this is a frequent need, I'd create a view that contains the columns you're interested in.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
0

I don't believe this is possible.

twerq
  • 528
  • 4
  • 11
0

This is not possible without writing another query to loop over the column names.

If you know which columns you need, you should SELECT them by name. If not, you should SELECT *.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
0

You have to list all the names I'm afraid. Assuming this is a permanent database object (e.g. table, view) then in Management studio you can right click the object in the tree view and choose SCRIPT TABLE AS -> SELECT to avoid typing them all.

Or alternatively drag the "columns" folder into your query window to get the comma delimited list of column names added.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845