CODEWITHSUNDEEP
sql-server
user418433
user418433

Reputation: 11

Fetch the all the column except one

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

Upvotes: 1

Views: 562

Answers (5)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

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

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453142

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.

Upvotes: 0

SLaks
SLaks

Reputation: 887415

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

Upvotes: 0

twerq
twerq

Reputation: 528

I don't believe this is possible.

Upvotes: 0

Related Questions