1

I need to select 90 columns out of 107 columns from my table.

Is it possible to write select * except( column1,column2,..) from table or any other way to get specific columns only, or I need to write all the 90 columns in select statement?

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
Shahsra
  • 981
  • 6
  • 18
  • 30
  • 2
    @Brandon - sometimes it can't be helped. I have a lot of tables with 150ish columns, because that's how we get it from our clients. It represents the raw client data and we need to keep it in the form we received it. We build normalized data off of this to actually use. – JNK Mar 24 '11 at 14:53
  • 1
    @Brandon - There could be several situation where 100+ columns may be needed. – Ash Burlaczenko Mar 24 '11 at 14:55
  • 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) – Martin Smith Mar 24 '11 at 14:58
  • You need to take a close look at **[SQL Prompt](http://www.red-gate.com/products/sql-development/sql-prompt/)** - you can type `SELECT * FROM dbo.Table` and place your caret after the star, press `TAB` and you get the whole list of columns read in from the database. VERY handy and extremely useful - wouldn't want to be without it anymore! – marc_s Mar 24 '11 at 15:08

6 Answers6

8

You could generate the column list:

select  name + ', '
from    sys.columns
where   object_id = object_id('YourTable')
        and name not in ('column1', 'column2')

It's possible to do this on the fly with dynamic SQL:

declare @columns varchar(max)

select  @columns = case when @columns is null then '' else @columns + ', ' end +
            quotename(name)
from    sys.columns
where   object_id = object_id('YourTable')
        and name not in ('column1', 'column2')

declare @query varchar(max)
set @query = 'select ' + @columns + ' from YourTable'
exec (@query)
Andomar
  • 232,371
  • 49
  • 380
  • 404
2

No, there's no way of doing * EXCEPT some columns. SELECT * itself should rarely, if ever, be used outside of EXISTS tests.

If you're using SSMS, you can drag the "columns" folder (under a table) from the Object Explorer into a query window, and it will insert all of the column names (so you can then go through them and remove the 17 you don't want)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
2

There is no way in SQL to do select everything EXCEPT col1, col2 etc.

The only way to do this is to have your application handle this, and generate the sql query dynamically.

Mike Lewis
  • 63,433
  • 20
  • 141
  • 111
2

You could potentially do some dynamic sql for this, but it seems like overkill. Also it's generally considered poor practice to use SELECT *... much less SELECT * but not col3, col4, col5 since you won't get consistent results in the case of table changes.

Just use SSMS to script out a select statement and delete the columns you don't need. It should be simple.

JNK
  • 63,321
  • 15
  • 122
  • 138
2

No - you need to write all columns you need. You might create an view for that, so your actual statement could use select * (but then you have to list all columns in the view).

MacGucky
  • 2,494
  • 17
  • 17
1

Since you should never be using select *, why is this a problem? Just drag the columns over from the Object Explorer and delete the ones you don't want.

HLGEM
  • 94,695
  • 15
  • 113
  • 186