SQl select specific column but with use of *

 

I need help, my SQL Server select statement is: 

select * from schematemplate.scanner

the columns of this table are: 

id
Asset_Category
Asset_Classification
Brand
Model
Supplier
Color

I can select all the columns except the Asset_Category and Asset_Classification by using this: 

Select id, brand, model, supplier, color 
from schematemplate.scanner

But I don't want to specify the columns that I will select like the code above.

Is it possible to use SELECT * from schematemplate.scanner and add a code like EXCEPT asset_category and asaset_classification ?

You could do it dynamically, eg: 

declare @s varchar(max) = 'select '

select @s=@s+name+',' from sys.columns
where object_id=object_id('schematemplate.scanner')
  and name not in ('asset_category','asset_classification')
order by column_id

set @s=substring(@s,1,len(@s)-1)+' from schematemplate.scanner'

exec(@s)

sqlfiddle

Those are only five columns. Why not select it?

Anyway, here's a suggestion that you may take,

  • create a view and
  • run select on it,

    • example 

    CREATE VIEW viewScanner
AS
SELECT id, brand, model, supplier, color 
FROM   schematemplate.scanner

    and when you want to select records, 

    SELECT * FROM viewScanner
    链接地址: http://www.djcxy.com/p/93856.html

    上一篇: 如何从查询中检索列数据类型？

    下一篇: SQl选择特定的列，但使用*