3

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 3
    There are many topics on this. The answer is no. You can create a view as suggested below. – Kermit Feb 04 '13 at 03:52
  • @PinnyM- because my tables are changing the column names and no. of columns, i want a select statement that will select all except asset_category and asset_classification. –  Feb 04 '13 at 03:58
  • @njk - ok now i know that i cannot do that kind of statement, thank you –  Feb 04 '13 at 03:58

2 Answers2

3

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
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • thanks but this answer is not helping and i am not lazy, the reason is that may table is changing the number of columns, its not always the id, brand, model, supplier, color. It can be id, brand, Part_no, Size. That is why i cant a statement that will select all columns except the asset_category and asset_classification –  Feb 04 '13 at 03:55
  • *changing the number of columns* -- physically? or you only mean during the projection of the result? – John Woo Feb 04 '13 at 03:57
  • hmm to be more clearly, In my code, there is a button that will select the column depending on what the user choose from the dropdownlist. here is the code: select * from schematemplate."& dropdownlist.text &" that's why i cannot use what you suggest –  Feb 04 '13 at 04:01
  • @RoronoaZoro Are you able to `SELECT` the column names to populate the drop down list and just omit the columns you don't want? Take a look at [this question](http://stackoverflow.com/questions/1054984/get-columns-of-a-table-sql-server). – Kermit Feb 04 '13 at 04:05
  • 1
    in your front-end, why not create a string that concatenates the selected columns on the dropdownlist.text? – John Woo Feb 04 '13 at 04:07
  • @njk - i am not populating the dropdown by the use of select –  Feb 04 '13 at 04:36
0

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

gordy
  • 9,360
  • 1
  • 31
  • 43
  • 1
    @RoronoaZoro it may have been from the schema name. I've edited the answer to use "schematemplate.scanner" can you give it a try? – gordy Feb 04 '13 at 04:52
  • still have error but i replace the other one with "schematemplate.scanner". And its finally working!!! Thank you very much sir this is what I am looking for! I will accept it as an answer and +1! thanks again :D –  Feb 04 '13 at 04:56