SELECT * FROM TABLE -- except for these columns

<< Becoming a Better Developer, plus a SQL Crime Drama! | Home | More on GROUP BY; Examining SUM(Distinct) >>

SELECT * FROM TABLE -- except for these columns

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 *. )

Print | posted on Thursday, July 26, 2007 3:36 PM

Feedback

# re: SELECT * FROM TABLE -- except for these columns

IMO, it should throw an error

7/26/2007 5:06 PM | Mladen

# re: SELECT * FROM TABLE -- except for these columns

Jeff, no need to script, just drag the columns folder into the query window, I wote about this trick almost 2 years ago
works in QA and SSMS

http://sqlservercode.blogspot.com/2005/09/query-analyzer-trick.html

7/26/2007 9:01 PM | Denis the SQL Menace

# re: SELECT * FROM TABLE -- except for these columns

Nice! Thanks, Denis!

7/26/2007 9:40 PM | Jeff

# re: SELECT * FROM TABLE -- except for these columns

i thought everone knew about that... :))

7/27/2007 9:27 AM | Mladen

# re: SELECT * FROM TABLE -- except for these columns

Mmmmm since that was new, how about this one?

http://sqlservercode.blogspot.com/2007/01/increase-your-productivity-with-query.html

no one knew this one at my job

7/27/2007 9:55 AM | Denis the SQL Menace

# re: SELECT * FROM TABLE -- except for these columns

Frankly, the only time someone should be selecting * is for some adhoc data discovery; in which case, the inclusion of some extraneous columns shouldn't matter.

Production level code should explicitly list the columns to be returned. The principal reason for this is to insure against schema changes. When a field is added to a table, select * will return it -- sometimes with unexpected results.

With the myriad of IDEs available for database developers, including the entire list of columns should not be a burden

7/27/2007 9:57 AM | Bob

# re: SELECT * FROM TABLE -- except for these columns

Thanks, Bob, I think we all agree with that. The point of the post wasn't to debate the merits of SELECT * from a table, as I tried to indicate.

7/27/2007 10:30 AM | Jeff

# re: SELECT * FROM TABLE -- except for these columns

If you run a query like that often you should create a view that gives you just what you want. Otherwise specify what you want when you type the query. You can even create sql files with the queries that you use often. But I don't see a compelling reason for the except clause.

7/27/2007 11:20 AM | Brian

# re: SELECT * FROM TABLE -- except for these columns

I use "Denis the SQL Menace"'s process to avoid the "SELECT *" : in Mgmt Studio (same applies to Query Analyzer) highlight the table you're doing the SELECT on, press CTLR+D (to put the results in grid-mode), then press ALT+F1 (to get the table metadata) then highlight the column_name column in the 2nd table & you can paste it into your query window & do the grunt work of replacing newlines with ", " to build the column list.

I also remap CTRL+1 keystroke to sp_helptext so I can quickly view the contents of views/functions/sprocs w/out having to navigate the object explorer tree.

7/27/2007 2:27 PM | jeffro

# re: SELECT * FROM TABLE -- except for these columns

IT IS VERY GOOD

7/28/2007 11:04 AM | vijay

# re: SELECT * FROM TABLE -- except for these columns

Good Post !
But , **SELECT * FROM TABLE** will leads to performance issue. be sure to use the column name to fetch the information from the DB.

8/15/2007 6:44 AM | Anand Muthu

# re: SELECT * FROM TABLE -- except for these columns

i have doubt.. how to selest a particular table and that particular table how to selest a particular data..

12/21/2007 2:13 PM | chandra ganapathi

# re: SELECT * FROM TABLE -- except for these columns

is there's a way to select a table using a variable? I trie SELECT * FROM $var

7/17/2008 9:48 PM | Web designer

Post Comment

Title
Name
Email
Url
Comment
Remember Me?
Please add 2 and 4 and type the answer here: Enter the code shown above:

Jeff's SQL Server Blog

My Links

SQLTeam.com Links

News

Archives

Post Categories

Programming

Sports

SQL