A occasional question seen in the forums, which was just recently asked today, is:
"I know I can use
SELECT *
FROM table
to get all of the columns from a table, but is there a way to write
SELECT * minus columnX
FROM Table
to get all of the columns except for certain ones?"
Now, my goal isn't to debate whether not "SELECT *" is bad or good or should be used or not. The fact of the matter is, people use it all the time because it is quicker and shorter than typing out all of the column names. People are lazy, right? (myself included.) And these lazy people often would like to return all of the columns from a table except for one or two without typing them all out. So, why isn't this concept allowed? Would it make any sense?
Here's my usual response:
First, the answer is no, SQL does not support that. You must specify what you want.
Second, if you ask me "why not? It would be great!", let me ask you this in return:
"If SQL did support that syntax, and you executed
SELECT * minus ColumnX
FROM Table
but columnX did not exist in that table, what should happen?"
I think it's kind of an interesting thing to think about. Should an error occur? Or should it just happily return the results, since we didn't want ColumnX anyway? Maybe it should issue a warning? I really don't know ... Would we all ever be able to agree on a definitive, logical way to handle this?
Here's another way to think about it: What if there were a command called "DontExec" that simply
didn't execute the stored procedure specified. What should happen if you call DontExec on a stored procedure that doesn't exist?
That sounds kind of silly, I know, but it is the same basic thing to consider as if you had a "* minus Column" option in a SELECT statement. Shorter to type? Sure. Does it make any sense? Not really.
By the way -- the third part of my response is usually this: the easiest solution is to simply use SSMS, QA, or EM to assist you in building your column list by using the scripting features or the query builder if you don't want to type it all out.
(
UPDATE: As Denis points out in the comments, it's actually even
easier. Now you
really have no excuse to use *. )