CODEWITHSUNDEEP
sqlsql-serverselect
user1987631
user1987631

Reputation:

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?

Upvotes: 3

Views: 325

Answers (2)

gordy
gordy

Reputation: 9796

You could do it dynamically, e.g.:

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

Upvotes: 0

John Woo
John Woo

Reputation: 263763

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

Upvotes: 3

Related Questions