Clever Geek Handbook

SQl select specific column but using *

I need help, my SQL Server select statement:

select * from schematemplate.scanner

columns of this table:

id
Asset_Category
Asset_Classification
Brand
Model
Supplier
Color

I can select all columns except Asset_Category

and Asset_Classification

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.

Can I use SELECT * from schematemplate.scanner

and add type code EXCEPT asset_category and asaset_classification

?

+3


source to share


2 answers


You can do it dynamically, for example:

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

0


source


There are only five columns. Why not choose it?

Anyway, here's an offer you can accept,

  • create a view and
  • run select,

Example



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

and when you want to select records,

SELECT * FROM viewScanner
+3


source






More articles:


All Articles