Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Function Gotcha with the Use of an Asterisk


A Function Gotcha with the Use of an Asterisk

Author
Message
Lashams
Lashams
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 48
Comments posted to this topic are about the item A Function Gotcha with the Use of an Asterisk



leon-679801
leon-679801
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
I simply do a "select top 1 * from table" and return the results in text. Then copy the first row which is the fieldnames, paste it into the query window and do 2 "find > replaces". First replace all spaces with double commas and then replace all double commas with single comma. This gives me a list of the fields seperated by a comma.

just a suggestion...
Richard Gaston
Richard Gaston
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 75
I use the following easy-to-memorize SQL to get column lists:

select ', ' + name from syscolumns where id = object_id( 'MyTable' ) order by colorder

or

select ', ' + column_name from information_schema.columns where table_name = 'MyTable' order by ordinal_position



Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445
Great article, except that you (and the other posters) either don't use Query Analyzer or SSMS to write your queries, or you do and aren't aware of a neat trick...

In either tool you have a query open where you want the column names of a table to appear. You simply navigate to the table using the tree on the left of the screen. Then expand the + sign next to the table so that you have the usual Columns, Indexes, etc folder headings beneath the table. You can drag and drop the "Columns" folder to your query and PRESTO, the column names appear. MUCH easier Tongue

You can also drag & drop the table name for long names - it will even prefix with dbo. (or other schema if not dbo obviously).

Since I've been using the drag & drop technique I have always steered clear of the select * but I can understand using the shorthand if you weren't aware of drag/drop.

Anyhow, good description of the problem. I wonder if you happened to have a string field push over into an integer return column if you might have noticed the problem sooner (because the string wouldn't go into an int column...).



ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Thats a neat trick Ian. Where did you get it from ?

"Keep Trying"
leon-679801
leon-679801
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
For a few fields, drag and drop is fine but if you have a lot of fields and want all of them you have to drag, drop and then click off the field name to add a comma. Then drag, drop etc, etc

Actually for a few bucks I now use SQL prompt unless I need the entire table fields.
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445
Leon, I think you misunderstood. Please try it - if you drag "Columns", not an individual field itself, then you'll get them all transferred. Each is separated by a comma. Only thing you may wish to do is put a few line breaks in every third or forth field I suppose. Using Ctrl+ArrowKeys and your enter key makes short work of that.



leon-679801
leon-679801
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
Thx Ian - I understand now. I have never used that - simple things you somehow miss on the way... w00t
Malcolm Currey
Malcolm Currey
SSC-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 Visits: 110
Another way of getting a select query quickly using Query Analyzer is also to use the Object browser window on the left, right click on a table name, and select one of the "Script Object to..." options. For a select query, I usually use "Script Object to Clipboard As" then choose "Select" from the drop-down menu, then paste the query to my query window. This method includes "[" and "]" around each object name, plus the "dbo." in the table name.

Mal
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445
No problem Smile



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search