How to select some particular columns from a table if the table has more than 100 columns

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 t开发者_如何学JAVAhe 90 columns in select statement?

---

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)

---

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)

---

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.

---

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.

---

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

---

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.