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
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.
Red Gate Software Ltd.
Comments
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
-Project Manager
-Red Gate Software Ltd
We have a lot of XML document exchange, so it is more easy becouse XML is case sensitive.
regards,
radovan
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.
But additionaly we use:
1) spaces before keywords
2) Nested joins to show join order
this: To This:
This: To This:
Becomes: where as my rules should have it become:
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".
---BECOMES---
Because of the HTML window size limit, this might not appear quite right on your screen but I hope you see the idea.
I get this:
But what I'd expect is:
Also, I'm wondering if it would be possible to hotkey between layout modes, for instance there are times when I want to use
instead of
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.
Change the above to
Similarly get rid of the redundant FROM in DELETE FROM
Change to
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.
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. :P
SQL Developer
HCA Physician Services
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
Hi,
this one is actually a bug, and this will be fixed in version 1.1. I will also contact you concerning this in a private message. Many thanks,
Andras
Red Gate Software Ltd.
I would like to always have trailing spaces removed, or at least have it as an option.
simplistic example:
before:
after:
That aside, I would like to see an option whether or not to indent (Assuming I did not miss this feature). So, the option would work like this:
Option 1 (No Indent)
Option 2 (With Indent)
Cheers!
to one with spaces after the operator:
I realize it is only whitespace, but I thought I'd mention it.
http://www.solidrockstable.com/blogs/PragmaticTSQL
BEGIN TRAN would increase the indent level. Knowing when to decreasing it would be a but tricky to determine, as there may be conditional logic involved.
DECLARE
@lProcedureName .VARCHAR(100),
@lReturnCode .......INTEGER
@lValue .................INTEGER
SELECT
@lProcedureName. = 'TestProcedure,
@lReturnCode....... = 0,
@lValue................. = 123
I am testing SQL Refactor and I suggest two more features:
1. I would prefer that the object names and column names would be formatted with case sensitivity.
2. And I would like to have the squared brackets set automatically to object and column names like SELECT [EmployeeID], [Title], [NationalIDNumber] FROM [HumanResources].[Employee];
Thank you!
You cannot achieve this layout with the current version of Refactor, which is a shame. Although, this is unsurprising considering the many layouts being utilised in the industry, I can see a much wider audience making use of it if it was able to layout the code in the same format as the help files.
Probably the most imortant changes to assist in this would be:
1. The ability to align comments with the next line of code
2. The ability to have BEGIN END blocks align directly under the keyword it serves
3. The ability to list colums horizontally in SELECT statements, yet vertically in modification statements (UPDATE and INSERT)
4. The ability to decide which keyword types should be uppercased (for example, uppercase all except datatypes)
There are a few others, which can easily be spotted by copying SQL Server example code and laying it out to see the changes.
If Refactor ever becomes as flexible as SQL Prompt, then it would be an invaluable asset in laying out the code. As for now, I use refactor solely for its other features, like Smart Rename, Script Summary etc. I have just purchased both the SQL Compare Bundle and the SQL Prompt Bundle, so I am a big fan of Red-Gate tools...but I know I could encourage many more of my clients to invest in the SQL Prompt Bundle when the layout feature matches the flexibility of Prompt!
For example:
I would like to be able to display it this way:
exec dbo.spS
@xdoc=@xdoc
, @xmldata = @xmldata
Currently it is laying out like this
exec dbo.spS @xdoc=@xdoc, @xmldata = @xmldata
- Spaces before keywords
- Nested Joins to show join order
- align commas with columns
- indent line comments with next or previous line
- remove trailing spaces
- object name and column name formated with case sensitivity
- Ability to select upper or lower case for certain objects (for example I like my commands to be in upper case but my objects maybe to be lower case)
I would prefer to have the joined table and condition on a new line, like this:
I think that is more consistent with the layout, as major keywords are alone on each line.
I would also prefer to use tabs over spaces for indents.
Place operator at start looks like this:
Place operator on line by itself would look like this: