Reverse of *
-
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.
-
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.
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 ofSELECT * EXCLUDE col1, col2 FROM tableNameis that, it will be forced to do a lookup then remove, Again, much efficient to list all the columns.Yusuf May I help you?
-
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.
-
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 ofSELECT * EXCLUDE col1, col2 FROM tableNameis that, it will be forced to do a lookup then remove, Again, much efficient to list all the columns.Yusuf May I help you?
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... :)
-
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:
-
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... :)
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
-
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.
Use Red-Gate sql prompt, type ssf [tab] expands to
Select *
Fromtype 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 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