Expand SELECT *

brucekillam · July 24, 2007 11:48AM

I have a script that generates queries and quite often these queries contain many joins. When I copy this query into a new query window in SSMS and place my cursor after the '*' and hit tab, only columns from the first two tables are expanded (many of my queries have well over 5 joins). To get it to work correctly, I have to place the cursor at the very end of the query, put a space at the end, and then when I perform the expand operation, I see all of the columns from all of the joined tables.

It was maddening and time consuming to try and figure out how to get the expand functionality to work correctly in this scenario, but once I figured out a work around, it isn't too difficult to do. However, you might want to see if you can duplicate the problem and isolate the bug.

Bart Read · July 24, 2007 12:08PM

Interesting. Thanks for letting us know about this Bruce. We'll take a look into it. It kind of sounds like SQL Prompt might not be parsing the entire query. Tell me, without applying your workaround, if you just put the cursor to the left of the * and force the candidate list with CTRL+SPACE do you see all the tables, aliases and columns you'd expect in the list?
Thanks,
Bart

Bart Read · July 24, 2007 12:08PM

Also, is there any chance you could post an example query where this problem occurs on here please?
Thanks,
Bart

brucekillam · July 25, 2007 10:00AM

Without applying my workaround, if I put the cursor to the left of the * and try to force the candidate list with CTRL+SPACE it offers no suggestions.

An example of this would be the following query (run from the ReportServer db):

SELECT * FROM &#91;ReportSchedule&#93; P10tbl
    JOIN &#91;Catalog&#93; R17fk ON P10tbl.ReportID = R17fk.ItemID
    JOIN &#91;Subscriptions&#93; R18fk ON P10tbl.SubscriptionID = R18fk.SubscriptionID
    JOIN &#91;Schedule&#93; R19fk ON P10tbl.ScheduleID = R19fk.ScheduleID

Copy from the first character of the query to the last and paste it into a new query window (I'm using SSMS) and put the cursor after the * and hit tab. What I get is the following (notice that it only gives columns for the first two tables - P10tbl and R17fk; R18fk and R19fk seem to be ignored):

SELECT P10tbl.&#91;ScheduleID&#93;,
	   P10tbl.&#91;ReportID&#93;,
	   P10tbl.&#91;SubscriptionID&#93;,
	   P10tbl.&#91;ReportAction&#93;,
	   R17fk.&#91;ItemID&#93;,
	   R17fk.&#91;Path&#93;,
	   R17fk.&#91;Name&#93;,
	   R17fk.&#91;ParentID&#93;,
	   R17fk.&#91;Type&#93;,
	   R17fk.&#91;Content&#93;,
	   R17fk.&#91;Intermediate&#93;,
	   R17fk.&#91;SnapshotDataID&#93;,
	   R17fk.&#91;LinkSourceID&#93;,
	   R17fk.&#91;Property&#93;,
	   R17fk.&#91;Description&#93;,
	   R17fk.&#91;Hidden&#93;,
	   R17fk.&#91;CreatedByID&#93;,
	   R17fk.&#91;CreationDate&#93;,
	   R17fk.&#91;ModifiedByID&#93;,
	   R17fk.&#91;ModifiedDate&#93;,
	   R17fk.&#91;MimeType&#93;,
	   R17fk.&#91;SnapshotLimit&#93;,
	   R17fk.&#91;Parameter&#93;,
	   R17fk.&#91;PolicyID&#93;,
	   R17fk.&#91;PolicyRoot&#93;,
	   R17fk.&#91;ExecutionFlag&#93;,
	   R17fk.&#91;ExecutionTime&#93; FROM &#91;ReportSchedule&#93; P10tbl
    JOIN &#91;Catalog&#93; R17fk ON P10tbl.ReportID = R17fk.ItemID
    JOIN &#91;Subscriptions&#93; R18fk ON P10tbl.SubscriptionID = R18fk.SubscriptionID
    JOIN &#91;Schedule&#93; R19fk ON P10tbl.ScheduleID = R19fk.ScheduleID

JasonC · July 27, 2007 7:23AM

Hi Bruce. Unforunately, I cannot replicate this bug. Are you using SQL prompt 3.5? This is the latest version.

The below query on Adventureworks I think is similar to yours:

SELECT * FROM [Person].[Contact] P10tbl
JOIN [Sales].[StoreContact] R17fk ON P10tbl.[ContactID] = R17fk.[ContactID]
JOIN [Sales].[Individual] R18fk ON P10tbl.[ContactID] =R18fk.[ContactID]
JOIN [Sales].[ContactCreditCard] R19fk ON P10tbl.[ContactID] = R19fk.[ContactID]

but I can expand the star with no problems at all.

Expand SELECT *

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk