How to select some particular columns from a table

问题:

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?

回答1:

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)

回答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)

回答3:

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.

回答4:

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.

回答5:

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

回答6:

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.