Fetch the all the column except one

Question

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

Answer 1

I don't believe this is possible.

Answer 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

Answer 3

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

Answer 4

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.

Answer 5

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