Reverse of *

[Ray Cassick]

I am just sitting here running some manual queries against some DB tables and thought that it would be GREAT if we could get a token that somehow meant the reverse of what * means in a select query. SELECT * FROM [TableName] Gets us ALL the columns, so if we want to grab a majority, but just skim off a few we need to enter in all of them leaving off the few we don't want. Why not allow us to do something like this? SELECT * EXCLUDE FieldName1, ... FROM [TableName] This seems much cleaner to me. Oh yeah, and it is less typing. I know, I should be using a tool that allows me to do my queries in a GUI and then it would just be drag and drop to get the columns I want, but we don't always have that luxury. It seems to me that the intent would be much more explicit to the DB engine and thus provide better optimization also.

---

LinkedIn[^] | Blog[^] | Twitter[^]

[Yusuf]

You know, you ain't getting that ;P Generally speaking it is better to list all your columns instead of using SELECT *. It does not have to do unneccessery lookup and your contract is fixed. You know what you will be getting. So, one disadvantage of SELECT * EXCLUDE col1, col2 FROM tableName is that, it will be forced to do a lookup then remove, Again, much efficient to list all the columns.

Yusuf May I help you?

[Lost User]

Why not write a query that returns all fieldnames ex the ones you specify, and copy/paste the result into your script? :) (Second hint, you could automate that task if it's important enough to spend time on)

I are Troll :suss:

[Ray Cassick]

Yusuf wrote:

You know, you ain't getting that

Yeah, I know :) Just one of those things that popped into my head as I was messing around. When you are doing manual querries, just messing around in a DB, getting to know it, testing relations, etc... you do 'goofy' things that save you typing :) yeah, 'SELECT *' is lazy but... :)

---

LinkedIn[^] | Blog[^] | Twitter[^]

[Ray Cassick]

Sheesh, sounds like work :) yeah, yeah... I know... Just ramblings of a tired mind on a Friday afternoon.

---

LinkedIn[^] | Blog[^] | Twitter[^]

[Mycroft Holmes]

Ray Cassick wrote:

popped into my head

Go wash out you mind, preferably with beer is is starting to stray into fantasy land again.

Never underestimate the power of human stupidity RAH

[Mycroft Holmes]

Use Red-Gate sql prompt, type ssf [tab] expands to

Select *
From

type the table name, press up arrow and then [tab], all column name are placed in a list, remove the offending column. With the amount of time I spent in SSMS my fingers have this in memory. [edit] bloody html markup [/edit]

Never underestimate the power of human stupidity RAH

[Ray Cassick]

:)

---

LinkedIn[^] | Blog[^] | Twitter[^]