A Function Gotcha with the Use of an Asterisk

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Stephen Lasham » A Function Gotcha with the Use of an Asterisk...

152 posts, Page 1 of 16

1 2 3 4 5 »»»

A Function Gotcha with the Use of an Asterisk

Rate Topic

Display Mode

Topic Options

Author

Message

Lashams

Posted Tuesday, April 29, 2008 9:29 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, August 01, 2011 2:05 PM
Points: 32, Visits: 47

Comments posted to this topic are about the item A Function Gotcha with the Use of an Asterisk

Post #492597

leon-679801

Posted Tuesday, April 29, 2008 10:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 24, 2010 10:17 PM
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...

Post #492612

Richard Gaston

Posted Tuesday, April 29, 2008 10:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 3:57 PM
Points: 4, Visits: 45

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

Post #492624

Ian Yates

Posted Tuesday, April 29, 2008 11:16 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

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 :P

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

Post #492633

ChiragNS

Posted Tuesday, April 29, 2008 11:26 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

Thats a neat trick Ian. Where did you get it from ?

"Keep Trying"

Post #492640

leon-679801

Posted Tuesday, April 29, 2008 11:26 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 24, 2010 10:17 PM
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.

Post #492641

Ian Yates

Posted Tuesday, April 29, 2008 11:30 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

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.

Post #492643

leon-679801

Posted Tuesday, April 29, 2008 11:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 24, 2010 10:17 PM
Points: 3, Visits: 11

Thx Ian - I understand now. I have never used that - simple things you somehow miss on the way... w00t

Post #492646

Malcolm Currey

Posted Tuesday, April 29, 2008 11:36 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 01, 2012 9:38 PM
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

Post #492648

Ian Yates

Posted Tuesday, April 29, 2008 11:37 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

No problem :)

Post #492649

« Prev Topic | Next Topic »

152 posts, Page 1 of 16

1 2 3 4 5 »»»

Permissions