Possible to exclude or reorder a column from `*`? [duplicate]

This question already has answers here: Closed 12 years ago.

Possible 开发者_C百科Duplicate:

SQL exclude a column using SELECT * [except columnA] FROM tableA?

Is it possible to exclude a column from a select * from table statement with SQL Server?

I have a need for this and this is my only option other than parsing a raw SQL string to get out the required field names (I really don't want to do that).

Just to be bold. When the query is made I do not have access to the list of fields needed from the table but I do know which field I do not need. This is part of a complex multi-part query.

Surely there must be some way even if it's "hackish" such as using table variables or views

My other option is to reorder the columns. My problem is with ExecuteScalar SQL functions which get the first row and first column.

EDIT

I can't add an answer since this is now closed but the way I ended up doing it was like so:

;with results_cte as (
  select (calculation) as calculated_column, * from table
)
select * into #temptable from results_cte
where calculated_column<10 /*or whatever*/

alter table #temptable
  drop column calculated_column

select * from #temptable
drop table #temptable

---

Nope. You'll have to build your statement manually or just select *.

---

No.

Instead, you could check syscolumns to get all of the field names, or (perhaps) SELECT * and ignore that column.

---

If you use dynamic SQL, you can generate the query from metadata about the table or view (INFORMATION_SCHEMA.COLUMNS) and exclude columns that way. I do this a lot to generate triggers or views.

But there is nothing in the SQL language which supports this.

---

The best way to handle this would be to select * and then just not present the excluded column to your users in your frontend. As others have noted, SQL has no direct capability of doing an all-columns-except construct.