What are the challenges you face when working across database platforms? Take the survey
Options

Expand SELECT *

brucekillambrucekillam Posts: 8
edited July 27, 2007 7:23AM in SQL Prompt Previous Versions
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.

Comments

  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    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
    Principal Consultant
    bartread.com Ltd
  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    Also, is there any chance you could post an example query where this problem occurs on here please?
    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    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 [ReportSchedule] P10tbl
        JOIN [Catalog] R17fk ON P10tbl.ReportID = R17fk.ItemID
        JOIN [Subscriptions] R18fk ON P10tbl.SubscriptionID = R18fk.SubscriptionID
        JOIN [Schedule] 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.[ScheduleID],
    	   P10tbl.[ReportID],
    	   P10tbl.[SubscriptionID],
    	   P10tbl.[ReportAction],
    	   R17fk.[ItemID],
    	   R17fk.[Path],
    	   R17fk.[Name],
    	   R17fk.[ParentID],
    	   R17fk.[Type],
    	   R17fk.[Content],
    	   R17fk.[Intermediate],
    	   R17fk.[SnapshotDataID],
    	   R17fk.[LinkSourceID],
    	   R17fk.[Property],
    	   R17fk.[Description],
    	   R17fk.[Hidden],
    	   R17fk.[CreatedByID],
    	   R17fk.[CreationDate],
    	   R17fk.[ModifiedByID],
    	   R17fk.[ModifiedDate],
    	   R17fk.[MimeType],
    	   R17fk.[SnapshotLimit],
    	   R17fk.[Parameter],
    	   R17fk.[PolicyID],
    	   R17fk.[PolicyRoot],
    	   R17fk.[ExecutionFlag],
    	   R17fk.[ExecutionTime] FROM [ReportSchedule] P10tbl
        JOIN [Catalog] R17fk ON P10tbl.ReportID = R17fk.ItemID
        JOIN [Subscriptions] R18fk ON P10tbl.SubscriptionID = R18fk.SubscriptionID
        JOIN [Schedule] R19fk ON P10tbl.ScheduleID = R19fk.ScheduleID
    
  • Options
    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.
    Jason Crease
    Red Gate Software
Sign In or Register to comment.