April 2, 2008 at 7:43 am
OK, apologies for what is probably a trivial post...
If you have a table with say 50 fields in it,
SELECT *
would return all fields and all rows.
If you wanted to return say 48 of these fields can you do something like:
SELECT a.*, -field1, -field2 FROM tblA
I know the above will not work, but hopefully you can see what I am trying to achieve.
The only work around I have atm is to SCRIPT TABLE, SELECT TO, CLIPBOARD and then delete the fields I don't want, not ideal as I often have to then add aliases to these fields etc.
Like I say, perhaps a pedantic question, but for a lot of the tables I work with it could be quite handy!
April 2, 2008 at 7:55 am
1. No, there is no way to do that. OK, Cess shows a way to do it below. But that's a TON of work when you can just not use * ... 😛
2. You shouldn't use * in the first place. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 2, 2008 at 7:55 am
Here's a stement to get you started for a dynamic sql query. It'll create a string of all fields in a table, minus the last 3:
DECLARE @Columns VarChar(2000)
DECLARE @NoColumns Int
SELECT @NoColumns = MAX(ordinal_position)
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'NorthWind'
SELECT @Columns = COALESCE(@Columns + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'NorthWind'
AND Ordinal_position <= (@NoColumns - 3)
print @Columns
print @NoColumns
SELECT column_name , *
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'NorthWind'
April 2, 2008 at 7:56 am
Unfortunately there's no easy way of doing this.
The truth is though, that you shouldn't really be doing a select * from...
You should identify each field that you want to return.
Not the answer you want to hear but it's best to follow best practices.
April 2, 2008 at 7:59 am
I don't tend to use * very often to be honest, as its rare I need everything in a table, however, why is it so bad to select everything in this way?
April 2, 2008 at 8:24 am
First, the problem that you're running into now.
If you have code that expects 15 columns, but suddenly gets 16, it may not know what to do with it and will likely generate an error or display the wrong data since the column order may have changed. You're asking for extra work from the sql engine to figure out what columns to move instead of explicitly telling it the columns you want. More often than not, people don't want all the columns in a table, but they use this shorthand to move them all anyway. That leads to a lot more I/O on your server and network than you would actually need. There are more.
The basic rule of thumb is to move only the data you need, only when you need it. Despite what most people think or say, they don't need everything, everytime.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 2, 2008 at 8:29 am
Ah, ok cool, ill get out of the habit of doing it then :p
As I said, it is not something I do that regularly anyway so no biggie.
Would it be fairly easy (or probably difficult!) to write something that pulled out every field in the table specified, prefixed every field with a letter or letters of your choice and put a comma and a space after each field?
I know nothing about dynamic SQL (except it uses '' instead of ' lol) but from what colleagues have shown me before I guess that's what I would have to use?
April 2, 2008 at 8:37 am
I've just taken Cees Cappelle's code, and modified it, that seems to do what I want 🙂
I'll be keeping that handy along with some other useful bits and bobs 😀
Thanks Cees Cappelle
DECLARE @Columns VarChar(2000)
DECLARE @NoColumns Int
SELECT @NoColumns = MAX(ordinal_position)
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'Preqin01'
SELECT @Columns = COALESCE(@Columns + ', ', '') + 'rr.'+column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'tblFirm'
print @Columns
print @NoColumns
SELECT column_name , *
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'tblfirm'
April 2, 2008 at 11:00 am
If I need a 'select' that has a lot of columns, I'll script out the source table 'create', then manually edit out the extra stuff.
Deleting the extra is usually easier and more accurate than typing all those column names, especially if they have a lot of funky abbreviations or underscores.
If it was easy, everybody would be doing it!;)
April 2, 2008 at 11:07 am
For that matter, if you right click a table in SSMS & use Script, Select, you can get a basic SELECT script for the table with all columns listed.
2008 will have type-ahead which will help.
You can also pick up SQL Prompt from Red-Gate.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 2, 2008 at 11:13 am
Trader Sam (4/2/2008)
If I need a 'select' that has a lot of columns, I'll script out the source table 'create', then manually edit out the extra stuff.Deleting the extra is usually easier and more accurate than typing all those column names, especially if they have a lot of funky abbreviations or underscores.
A little trick in Management Studio is you can drag-and-drop table and column names from the object explorer (usually on the left side of the screen), directly into the scripts you're writing. Then you won't misspell them or anything like that.
That's mainly useful if you only need a few columns, but it's quite handy when column/table name are tricky to type.
(You can do the same thing with procs, views, etc.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2008 at 11:19 am
OK, I'm feeling stupid here...
I knew the script 'select' was there, but for some dumb reason, I have been scripting the 'create' lately. Its as if I totally forgot script 'select' was there...:hehe:
I need a vacation...
If it was easy, everybody would be doing it!;)
April 2, 2008 at 11:24 am
Keep in mind that there's another trick, which doesn't include cut or paste.
Type in
Select * from Mytable
highlight the above, then right-click and "Design query in editor". Click Ok when the Editor opens up. and -voila, column names.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 2, 2008 at 4:43 pm
There's an even neater trick in Management Studio.
Expand the table, select the Columns folder and drag it into the query window - all columns are added in a comma-seperated fashion.
April 3, 2008 at 1:58 am
GSquared (4/2/2008)
Trader Sam (4/2/2008)
If I need a 'select' that has a lot of columns, I'll script out the source table 'create', then manually edit out the extra stuff.Deleting the extra is usually easier and more accurate than typing all those column names, especially if they have a lot of funky abbreviations or underscores.
A little trick in Management Studio is you can drag-and-drop table and column names from the object explorer (usually on the left side of the screen), directly into the scripts you're writing. Then you won't misspell them or anything like that.
That's mainly useful if you only need a few columns, but it's quite handy when column/table name are tricky to type.
(You can do the same thing with procs, views, etc.)
Yeah, I was quite pleased when I discovered that - pretty useful for objects with long or obscure naming conventions etc!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply