Improve SQL code formatting options
SQL Prompt 8.0 includes many improvements to the SQL formatting options. For additional information see: http://documentation.red-gate.com/display/SP8/SQL+Prompt+8.0+release+notes
You can get the latest version of SQL Prompt from http://www.red-gate.com/products/sql-development/sql-prompt/
Thank you for your help!
Eddy Kleinjan commented
Almost here (for me...). My style is:
LEFT OUTER JOIN A ON (or whatever) (
<tab>A.A = B.A
<tab>,A.B = B.B
So the closing bracket lines up with the lines above and uses a tab to indent. Could that be added?
Hi. I am testing SQL Prompt version 184.108.40.2063, and it still does not put opening bracket in the next line (1 TAB indented from parent line), when the line is very long. It just continues far far right in the next line. You can imagine how it looks when repeated 2 or 3 times, you get your cursor so far right that it is unreadable.
For example, this cannot be achieved, despite many options available (or I don't know how to achieve it with SQL Prompt):
----Result = sys.fn_replreplacesinglequoteplusprotectstring
And cannot achieve this:
I think it is a very basic rule when the expression does not fit in a single line:
1) put closing bracket vertically aligned with opening bracket
2) if you must put opening bracket in the NEW line, align it 1 TAB from parent expression indentation (do not continue at the SAME position you left off as it is now!)
I have downloaded and tried beta version 7.3.632 (from 24th October 2016), on query example from here (SQL can be copied from comments below): http://blog.sqlxdetails.com/formatting-tsql-code/
It can format exactly that style (my only change to default was to use TABS only),
but only in "Options" example.
If I try to format the same query in query editor, error occurs: "Error inserting semicolons. SQL Prompt encountered problems while processing this script"
Tim Goergen commented
Is there an updated link for where to post formatting examples? The link from Aaron Law on 6/11/2014 is no longer valid.
Few very useful formatting rules I follow are explained in this short video: https://www.youtube.com/watch?v=-ddvEZlz32Y
Okay, let's deal with SELECT command.
I like each clause (SELECT, FROM, WHERE, GROUP BY, HAVING, UNION, INTO, etc.) on a separate line, lined up with the SELECT statement.
I like each to be followed by a tab, vs. space.
I like each ON (in a JOIN), AND and OR on a separate line vs. inline (e.g. JOIN\tMyTable\nON\tcolumn... vs. JOIN myTable ON column...)
I'm not sure where it was but someone suggested that users could create a layout template that SQL Prompt could parse for settings. This sounds like a great idea if it could be accomplished.
Here are some samples of how I think SQL should be formatted:
We do keep an eye on these forums and they definitely influence what we decide to work on next! eg. The previous two top requests were for Tab Magic in SSMS2012 and synonym support (both 234 votes) which were added in Prompt 6 as a result of them being voted so highly here.
We should have a beta out for 6.4 tomorrow which will include features with well over 400 combined UserVoice votes (I’ll mark them as started with a link to the beta once it’s up)
This feature is an awkward one since “improve formatting” is vague and almost certainly means different things for different people. That said, it’s looking like formatting will probably be our next big focus on Prompt so discussions on this forums really help us when we define exactly what needs to be improved.
I would like to see feedback from Redgate from time to time on suggestions. Yeah, I know, sometimes some of these suggestions are major undertakings, but come on, the highest rated suggestion of all, with comments going back over three years. Are you listening or is this board just something for users to banter back and forth amongst themselves? Let us know you read these and whether or not you are taking any of our suggestions seriously or let us know we are blowing smoke.
I need more options for formatting lists of literals, and I may want them formatted separately, or in a different manner, than the SELECT list itself.
Also, I work with scripts from a vendor who has played fast and loose with any sort of code standards. I would like the ability I have seen in one other product to reformat to a standard, including things like adding "WITH" between a table/view name and "(NOLOCK)" if they've written it in there (don't ask--I've lost that battle with them). Also, automatically inserting "AS" between a field and its alias, or a table/view and its alias.
Biff Douglass commented
I agree with the comment by Doug (like the name), that subsequent AND/OR statements on a JOIN or WHERE should not be indented. They should be able to line up with the corresponding JOIN or WHERE clause.
With current options it is impossible to format sql the way I do it manually. And my way has simple logic. Blocks are surrounded by begin-end, so begin-end must be vertically aligned and contents indented. The same is with blocks surrounded by parenthesis, like inline view.
Blocks can begin with keywords like: SELECT (block is all the code until FROM), FROM (block is all the code until WHERE), WHERE, and contents of the block should be indented - until the next block-beginning keyword.
I use only tabs, never spaces. That's about it.
Can it be any simpler an more clear?
Here is example, very similar to one already posted here: http://pastebin.com/j0Y1Bra1
Here is a part of it:
____join t2 on ...
____join t3 on ...
______select 1 from ...
____) t4 on ...
__ORDER BY ...
So, keywords SELECT-FROM-WHERE are vertically aligned, open-closed brackets are vertically aligned, begin-end are vertically aligned. Simple, clear, very readable.
when there are subsequent conditional statements in either a JOIN or WHERE, the formatting is
WHERE~1 = 1
~~OR 2 = 2
WHERE~1 = 1
OR~~2 = 2
Yes, that would be hugely helpful if SQL Prompt had more options for the automatic re-formatting of code. Guys, take a look at JetBrains' ReSharper and be inspired by this absolutely out-of-this-world product :)
Yes, yes, yes to Stuart Branham's suggestion of creating a SQL query template and have SQL Prompt read it and determine formatting options!!!!
Leon Carpay commented
what would greatly help me is when I hand-format SQL-code, which is sometimes really helpfull, that SQL-Prompt leaves that part of the code alone.
The don't-touch-code could be surrounded by tags, eg
--<BEGIN DONT TOUCH SQL-PROMPT>
.... sql-code ...
--<END DONT TOUCH SQL-PROMPT>
Overall this application seems like it's half-finished. It's got so much potential, but it's only got "a few" formatting options. It simply needs a LOT more options.
Some people mentioned that it's a bad idea to have IF<CR><TAB>BEGIN. I think this is an understatement. It's flat **LUDICROUS** to have that excess tab in there. But if people want it, let them have it... but let the rest of us be able to turn it off!!
Another suggestion also made elsewhere here was to allow more formatting options of "sub-query joins" I consider that such a superbly good idea that I actually came here specifically to suggest it myself. Now instead I'll just repeat what other's have said... you have simply GOT to add the ability to put the opening ( on a new line rather than forcing it to be after the word "JOIN". Based on the vote numbers I'd guess at least 25 to maybe as many as 50 people came here to ask for that feature. And I'll bet at least 300 more didn't bother to come here but just uninstalled your demo when it wasn't available. Please strongly consider adding this extra-ASAP.
I always want to uppercase keywords when code is formatted. Please provide an option to enable this feature as a configuration option.
Adrian Tompkins commented
PIVOT keyword should start newline
Seth Phelabaum commented
After spending the past hour and a half reformatting a 2K line stored procedure that I had just formatted with SQL Prompt (It was probably going to take me that long to reformat it either way, I'm aware I could have just 'undone' the format), I came up with a list of specifics to add here. I read through all the other suggestions in this forum and see several of these repeated there, so it's good to know I'm not the only one who is heavily concerned with formatting code =).
IF/ELSE followed by BEGIN END, BEGIN is indented from the IF, and then things underneath the BEGIN are indented again, leading to entirely too much white space / extra indenting
BEGIN TRY should optionally not indent
INSERT/EXEC, INSERT/SELECT should not indent multiple tabs after the insert.
INSERT column lists: would be nice if you could the rows of the column list past the first one just a specified number of characters rather than to the end of the table name, as this can put things really far over on the row.
Comment lines begun with -- should indent to the level of the line below them. (to the level of the first noncomment line below it)
The option to line up things at the next tab stop as specified in this suggestion here: http://redgate.uservoice.com/forums/94413-sql-prompt-feature-suggestions/suggestions/2879097-align-aliases-commas-datatypes-and-expressions-a
Condensing of Code Options
An option to put the select list on a single line rather than multiple if it is short enough.
An option to put an entire select query on a single line if it is short enough should exist.
An option to put a single line IF statement (No begin following it) on a single line if it was short enough would be nice.
This one may be too much 'personal preference', but I much prefer CTE's to look more like either:
;WITH C(A,B) AS (
D (D1,D2) AS (
C(A,B) AS (
D (D1,D2) AS (