When working with data warehouses one has to often write queries against tables with many, many columns. The best practice tells us to always exactly specify the columns one wants in the SELECT query. Problem is that there are so many columns to write out that scrolling becomes excessive and tedious. I've been wondering... Could SQLPrompt somehow turn the stuff between SELECT and FROM collapsible with little effort on part of the dev? A keyboard shortcut would be perfect. This could also work for, say, UPDATE between SET and FROM. What about collapsible field in INSERT? I always write out all the columns that need inserting into...

When working with data warehouses one has to often write queries against tables with many, many columns. The best practice tells us to always exactly specify the columns one wants in the SELECT query. Problem is that there are so many columns to write out that scrolling becomes excessive and tedious. I've been wondering... Could SQLPrompt somehow turn the stuff between SELECT and FROM collapsible with little effort on part of the dev? A keyboard shortcut would be perfect. This could also work for, say, UPDATE between SET and FROM. What about collapsible field in INSERT? I always write out…

Instead it would be nice if the first TAB press would give:
a.*
,b.*
Pressing again without moving the cursor could expand all * to the same result as today, but if the cursor is changed to a specific *, then only that * would be expanded

I often finde my self joining 3,4,5 tables or more, and often just need a single column from the first and last tables

With multiple tables joined in a select, make it optional to have multi level expansion

Consider this Select:
Select *
from tblA as a
inner join tblB as b on b.fk_tblA_id = a.id

Instead it would be nice if the first TAB press would give:
a.*
,b.*
Pressing again without moving the cursor could expand all * to the same result as today, but if the cursor is changed to a specific *, then only that * would be expanded

This is actualy invalid sql as only the columns included in the group by can be in the select.
Instead it would be nice if the first TAB press would give the columns in the group by, so in this example just:
b.Value

I often finde my self adding ", count(*) rowCount" to the select, as It is always nice to have an idea of the data size biehind the group by, so an option to add some extra sql to the TAB-to-expand-wildcard-when-using-group-by would be nice

When adding group by to a select, it would be nice if the TAB-to-expand-wildcard would only include the columns in the group by

Consider this Select:
Select *
from tblA as a
inner join tblB as b on b.fk_tblA_id = a.id
group by b.Value

This is actualy invalid sql as only the columns included in the group by can be in the select.
Instead it would be nice if the first TAB press would give the columns in the group by, so in this example just:
b.Value

When expanding a * in
SELECT * FROM dbo.Objekt AS O JOIN dbo.ObjektArtikel AS OA ON OA.ObjektNr = O.ObjektNr AND OA.DelObjekt = O.DelObjekt AND OA.LeveransNr = O.LeveransNr
I get
SELECT O.ObjektNr ,
O.DelObjekt ,
O.LeveransNr ,
-- /.../
OA.ObjektNr ,
OA.DelObjekt ,
OA.ArtikelNr ,
OA.LeveransNr ,
-- /.../
Which is great, but that would produce an error if inserted directly into a table..
"Column names in each table must be unique. Column name 'ObjektNr' in table '#ArticlesBelongingToPreBundles' is specified more than once."

When expanding a * in
SELECT * FROM dbo.Objekt AS O JOIN dbo.ObjektArtikel AS OA ON OA.ObjektNr = O.ObjektNr AND OA.DelObjekt = O.DelObjekt AND OA.LeveransNr = O.LeveransNr
I get
SELECT O.ObjektNr ,
O.DelObjekt ,
O.LeveransNr ,
-- /.../
OA.ObjektNr ,
OA.DelObjekt ,
OA.ArtikelNr ,
OA.LeveransNr ,
-- /.../
Which is great, but that would produce an error if inserted directly into a table..
"Column names in each table must be unique. Column name 'ObjektNr' in table '#ArticlesBelongingToPreBundles' is specified more than once."

If there is no space between a wildcard (e.g., SELECT *) and the next statement, then when Format SQL expands the column list there will be no space between the last column and the next word, causing the script to error.

I found this out the hard way when trying to format a gnarly set of code from our offshore team - an example to reproduce the issue is below.

CREATE TABLE #MyTable (MyIntColumn INT, MyVarcharColumn VARCHAR(50))

SELECT *INTO #MyOtherTable -- note the lack of a space between the * and INTO
FROM #MyTable mt

DROP TABLE #MyTable
DROP TABLE #MyOtherTable

If there is no space between a wildcard (e.g., SELECT *) and the next statement, then when Format SQL expands the column list there will be no space between the last column and the next word, causing the script to error.

I found this out the hard way when trying to format a gnarly set of code from our offshore team - an example to reproduce the issue is below.

CREATE TABLE #MyTable (MyIntColumn INT, MyVarcharColumn VARCHAR(50))

SELECT *INTO #MyOtherTable -- note the lack of a space between the * and INTO
FROM #MyTable mt