| Author |
Message |
Andras
Joined: 19 May 2005
Posts: 249
Location: Cambridge, UK
|
 Posted: Thu Nov 02, 2006 11:00 am Post subject: Formatting requests - post them here |
 |
|
We support nearly forty options to customize laying out SQL, but is it enough? We had requests for many more 
So the purpose of this topic is to collect these requests. Please post your layout option requests here.
If you see a post with an option you like and would like to have this option too, please let us know via this forum, and the option request will have a higher weight when we decide on its inclusion.
Andras
_________________
András Belokosztolszki, PhD
Red Gate Software Ltd. |
|
| Back to top |
|
 |
almaz
Joined: 06 Nov 2006
Posts: 1
|
| Posted: Mon Nov 06, 2006 6:39 pm Post subject: |
|
|
| In our company we are using lowercase SQL syntax. It would be nice to have an option to convert SQL statements to lowercase. |
|
| Back to top |
|
|
Jonathan Watts
Joined: 15 Dec 2004
Posts: 375
Location: Red Gate Software
|
| Posted: Tue Nov 07, 2006 11:46 am Post subject: |
|
|
Hi there Almaz,
Yes, we were wondering how many people would want to use lowercase rather than uppercase. We will definately consider this for the next version.
Regards,
Jonathan
_________________
Jonathan Watts
-Software Test Engineer
-Red Gate Software Ltd
Last edited by Jonathan Watts on Wed Nov 08, 2006 11:18 am; edited 1 time in total |
|
| Back to top |
|
|
rdobrich
Joined: 14 Feb 2005
Posts: 40
|
| Posted: Wed Nov 08, 2006 8:17 am Post subject: |
|
|
In my company we are using lowercase too.
We have a lot of XML document exchange, so it is more easy becouse XML is case sensitive.
regards,
radovan |
|
| Back to top |
|
|
mrshrinkray
Joined: 13 Nov 2006
Posts: 1
|
| Posted: Mon Nov 13, 2006 4:27 pm Post subject: |
|
|
I layout my SQL in the following way:
| Code: |
SELECT
Table1.Col1,
Table1.Col2,
Table1.Col3
FROM
Table1
INNER JOIN Table2 On Table2.Col1 = Table1.Col1
WHERE
Table1.Col1 = 123
ORDER BY
Table1.Col1
GROUP BY
Table1.Col2 |
and I also use tabs. I can't see how this is possible, it would be grande if it could be considered including using tabs. |
|
| Back to top |
|
|
igorolv
Joined: 13 Nov 2006
Posts: 1
|
| Posted: Mon Nov 13, 2006 8:46 pm Post subject: |
|
|
SQL Refactor is really great thing. Thank you.
But additionaly we use:
1) spaces before keywords
| Code: |
select F1,
F2,
from Table1
where F1 > 0
and F2 < 1 |
2) Nested joins to show join order
| Code: |
select F1,
F2
from T1
join T2 on T2.ID = T1.T2_ID
join T3 on T3.ID = T2.T3_ID
join T5 on T5.ID = T3.T5_ID
join T4 on T4.ID = T1.T4_ID
|
|
|
| Back to top |
|
|
doclane
Joined: 21 Nov 2006
Posts: 3
|
| Posted: Tue Nov 21, 2006 10:53 pm Post subject: New feature |
|
|
Would like to align the commas with the columns
this:
| Code: |
SELECT
a.loanid
, b.businessdate
, customername = RTRIM(f.lastname) + ', ' + RTRIM(firstname)
, c.loannumber
|
To This:
| Code: |
SELECT
a.loanid
, b.businessdate
, customername = RTRIM(f.lastname) + ', ' + RTRIM(firstname)
, c.loannumber
|
_________________
Thomas M Lane |
|
| Back to top |
|
|
doclane
Joined: 21 Nov 2006
Posts: 3
|
| Posted: Tue Nov 21, 2006 11:08 pm Post subject: Feature request |
|
|
Indent line comments with next or previous line
This:
| Code: |
BEGIN
SET NOCOUNT ON
--set mostrecentstat flag to no in historical table
UPDATE
dbo.tbHistoricalDBFileInfo
SET
mostrecentstat = 0
WHERE
--move current to historical table with mostrecentstat flag set to yes
mostrecentstat = 1
|
To This:
| Code: |
BEGIN
SET NOCOUNT ON
--set mostrecentstat flag to no in historical table
UPDATE
dbo.tbHistoricalDBFileInfo
SET
mostrecentstat = 0
WHERE
--move current to historical table with mostrecentstat flag set to yes
mostrecentstat = 1
|
_________________
Thomas M Lane |
|
| Back to top |
|
|
doclane
Joined: 21 Nov 2006
Posts: 3
|
| Posted: Wed Nov 22, 2006 3:37 pm Post subject: Bug? |
|
|
I noticed that in DELETE statement the from clause does not follow the rules for next line.
| Code: |
DELETE FROM dbo.tbHistoricalDBFileInfo WHERE statdate < GETDATE() - 20
|
Becomes:
| Code: |
DELETE
FROM dbo.tbHistoricalDBFileInfo
WHERE
statdate < GETDATE() - 20
|
where as my rules should have it become:
| Code: |
DELETE
FROM
dbo.tbHistoricalDBFileInfo
WHERE
statdate < GETDATE() - 20
|
_________________
Thomas M Lane |
|
| Back to top |
|
|
Bodhi
Joined: 22 Nov 2006
Posts: 6
Location: Spokane, Washington
|
| Posted: Wed Nov 22, 2006 7:26 pm Post subject: Kernighan and Ritchie code lay-out |
|
|
I would like support for the layout described by Ken Henderson in "The Guru’s Guide to Transact-SQL".
| Code: |
IF condition BEGIN
SELECT . . .
END
ELSE
...
END
WHILE condition BEGIN
--take actions
. . .
END
CASE WHEN condition
THEN ...
ELSE ...
END |
This format is derived from the K&R book, "The C Programming Language". It makes code most readable by maximizing the "locality of the code". |
|
| Back to top |
|
|
Bodhi
Joined: 22 Nov 2006
Posts: 6
Location: Spokane, Washington
|
| Posted: Wed Nov 22, 2006 8:36 pm Post subject: Need better wrapping |
|
|
I have my Wrap text limit set to 78 characters. When I reformat with Lay Out SQL the lines don't wrap properly. Example.
| Code: |
SELECT TI.Column_Name, ISNULL(LD.LocalDescription,ISNULL(D.Descriptor, Ext.[Description])) [Description]
, TI.Alias, TI.AliasDescName
, TI.isInBisListView, TI.IsInBisValBar
, TI.ConstraintFilter, TI.ConstraintDescColumn
, data_type = CASE
WHEN C.DATA_TYPE = 'int' THEN 'integer'
WHEN C.DATA_TYPE LIKE '%char' THEN C.DATA_TYPE
+ '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR) + ')'
WHEN C.DATA_TYPE IN ('decimal', 'numeric')
THEN C.DATA_TYPE + '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR)
+ CASE C.NUMERIC_SCALE
WHEN 0 THEN ''
ELSE ','+CAST(C.numeric_scale AS VARCHAR)
END
+ ')'
ELSE C.DATA_TYPE
END
, C.DOMAIN_NAME
FROM information_schema.columns C
JOIN dbo.ft_view_sources(@table_name) AS v
ON C.TABLE_NAME=@table_name
AND v.view_column = C.COLUMN_NAME
-- further joins omitted here |
---BECOMES---
| Code: |
SELECT TI.Column_Name,
ISNULL(LD.LocalDescription,
ISNULL(D.Descriptor, Ext.[Description])) [Description],
TI.Alias, TI.AliasDescName, TI.isInBisListView,
TI.IsInBisValBar, TI.ConstraintFilter,
TI.ConstraintDescColumn, data_type = CASE WHEN C.DATA_TYPE = 'int' THEN 'integer'
WHEN C.DATA_TYPE LIKE '%char' THEN C.DATA_TYPE + '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR) + ')'
WHEN C.DATA_TYPE IN ('decimal', 'numeric') THEN C.DATA_TYPE + '(' + CAST(dbo.f_ruled_precision_of_column(@table_name, C.column_name) AS VARCHAR) + CASE C.NUMERIC_SCALE
WHEN 0 THEN ''
ELSE ',' + CAST(C.numeric_scale AS VARCHAR)
END + ')'
ELSE C.DATA_TYPE
END, C.DOMAIN_NAME
FROM information_schema.columns C
JOIN dbo.ft_view_sources(@table_name) AS v
ON C.TABLE_NAME = @table_name
AND v.view_column = C.COLUMN_NAME |
Because of the HTML window size limit, this might not appear quite right on your screen but I hope you see the idea. |
|
| Back to top |
|
|
monkeygrind
Joined: 21 Jun 2006
Posts: 28
Location: Seattle-ish
|
| Posted: Mon Nov 27, 2006 11:07 pm Post subject: |
|
|
Our shop is split - 50% use lowercase for keywords, 50% use uppercase. I did notice that there is some shift on an INSERT:
I get this:
| Code: |
insert into dbo.Promotion
select @promoID,
-- other params removed for brevity
getdate()
from dbo.Promotion
where promoID = @oldPromoID |
But what I'd expect is:
| Code: |
insert into dbo.Promotion
select @promoID,
-- other params removed for brevity
getdate()
from dbo.Promotion
where promoID = @oldPromoID |
Also, I'm wondering if it would be possible to hotkey between layout modes, for instance there are times when I want to use
| Code: |
insert into dbo.Company
(companyid, locationid)
values
(@companyid, @locationid) |
instead of
| Code: |
insert into dbo.Company
(
companyid,
locationid
)
values
(
@companyid,
@locationid
) |
Perhaps having setting modes, e.g. preferences 1 vs. preferneces 2. This would be ideal when different devs use slightly different standards in their DB's, or when helping someone in another group on their project where they have a different standard. |
|
| Back to top |
|
|
Bodhi
Joined: 22 Nov 2006
Posts: 6
Location: Spokane, Washington
|
| Posted: Mon Nov 27, 2006 11:29 pm Post subject: Option to omit redundant, optional key words |
|
|
I would like an option to get rid of the redundant INTO in
| Code: |
| INSERT INTO dbo.my_table (...) |
Change the above to
| Code: |
| INSERT dbo.my_table (...) |
Similarly get rid of the redundant FROM in DELETE FROM
| Code: |
DELETE FROM dbo.my_table
WHERE ... |
Change to
| Code: |
DELETE dbo.my_table
WHERE ... |
This will help me when I search through code for deletes or inserts on a particular table. It will also make the code nicely more compact.
I supose someone will want the reverse option to insert the redundant key words where they have been omitted. |
|
| Back to top |
|
|
DRBuckingham
Joined: 22 Sep 2005
Posts: 4
Location: Nashville, TN
|
| Posted: Fri Dec 01, 2006 9:34 pm Post subject: Joins with hints |
|
|
Currently the ON portion of the join clause aligns with the parenthesis in the join hint instead of with the table name (as it does when no join hint is present). If the current formatting is desired, then please provide an option to align it with the table name when a join hint is present, so it is consistent.
Also, it currently does not pad the inside of parenthesis of functions even though the option is set in the General/Spaces section. I would like to see that option added.
I also would very much like to see a single hot key option to apply the Qualify Object Names, Expand Wildcards, Uppercase Keywords, and Lay Out SQL options in that order (or user selectable as to which ones get applied or order). It would be of extreme benefit if it were available via a batch operation to clean up all our existing (inherited) code (3000+ scripts).
I would also like to see the option to prefetch (or cache) the meta data information necessary for the Qualify Object Names so that when I am doing multiple scripts I don't have to incur the lookups each time.
Perhaps an option to replace COALESCE with ISNULL when there are only 2 arguments would be another interesting option. Granted that exceeds a reformating operation, but it is food for thought. Another option along this line would be one to alias all columns in a select statement (styled like [DateOfBirth] = Patients.DateOfBirth rather than Patients.DateOfBirth AS [DateOfBirth] or vice versa).
I would like the ability to add to the list of keywords as well. Currently "nocount" among others are not considered keywords and won't get uppercased.
Having the ability to format BEGIN/ENDs like:
IF ... BEGIN
SET ...
END
Or at minimum not indenting the BEGIN/END like:
IF ...
BEGIN
SET ...
END
Another handy feature would be to column qualify the insert statements:
INSERT INTO dbo.MyTable
VALUES ( 1, 1, 'Test' )
would become:
INSERT INTO dbo.MyTable ( Column1, Column2, Column3 )
VALUES ( 1, 1, 'Test' )
Another nicety would be able to control formating of SP calls like:
EXEC dbo.MySP @Parm1, @Parm2, @Parm3,
@Parm4, @Parm5
would optionally become:
EXEC dbo.MySP
@Parm1,
@Parm2,
@Parm3,
@Parm4,
@Parm5
Also, I agree with most of the previous suggestions as well, except for the lowercasing of the keywords. 
_________________
David R Buckingham
SQL Developer
HCA Physician Services |
|
| Back to top |
|
|
vudang
Joined: 08 Dec 2006
Posts: 1
|
| Posted: Fri Dec 08, 2006 7:57 pm Post subject: |
|
|
I'm not sure if this is available as an option but I cannot get sql refactor to format the insert column list on one line when using insert select. Basically the following is not possible.
INSERT INTO #extract_tlot
(
entity_name, id_1,id_2,id_3
)
SELECT DISTINCT
tl.entity_name,tl.id_1,tl.id_2,tl.id_3
FROM sometable |
|
| Back to top |
|
|
|
