Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

A Function Gotcha with the Use of an Asterisk Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2008 9:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 1, 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
Posted Tuesday, April 29, 2008 10:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 29, 2008 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:44 AM
Points: 4, Visits: 58
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
Posted Tuesday, April 29, 2008 11:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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
Posted Tuesday, April 29, 2008 11:26 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Thats a neat trick Ian. Where did you get it from ?


"Keep Trying"
Post #492640
Posted Tuesday, April 29, 2008 11:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 29, 2008 11:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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
Posted Tuesday, April 29, 2008 11:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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...
Post #492646
Posted Tuesday, April 29, 2008 11:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 1, 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
Posted Tuesday, April 29, 2008 11:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
No problem :)


Post #492649
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse