SQL Code Formatter

Toad for SQL Server
1

Afternoon all

Just seeing if there are any plans to update the Code Formatter within Toad in a future release, not sure how others get on with it but I always find it rather cumbersome and not that user friendly

When you look at other add-ons for SSMS such as Red Gate’s SQL Prompt or Apex SQL Refactor they seem to offer a lot more options and are much easier and intuitive to set-up and use.

Many thanks

Karl

2

Hi Kal

The short answer is yes it is on the roadmap. We have some new additions to our Spotlight developer that should start to address this. I’d love to talk with you more about this and get your feedback.

What are the Dell Software products you use now?

Kathy Gibbs

kathy.gibbs@quest.com

3

Hi Karl,

I’m agree totally with you, about Code Formatter feature of TOAD for SS.

There are some competitors that works better and are more intuitive to use.

In past I’ve open many tickets about code formatter, but until now, nothing to do…

I’ll hope to see for the future more improvements in code formatter…

Best regards,

Sergio

4

Hi Sergio

As you know I am new to the role of Product Manager for Toad for SQL. I would love to set up a time to discuss the issues you have seen with the formatter so we can get these resolved in the product. Do you use Spotlight Developer at all?

If you are open to it, I’d love to set up a time that we can meet to discuss your concerns!

Are you going to be at PASS this year?

Kathy

kathy.gibbs@quest.com

5

Hi Kathy

Great to hear from you and the good news that you’re going to be addressing the Code Formatter options in the future.

Happy to help\test anything, you should have my email address if you’d like to drop me a line

Many thanks

Karl

P.S. I’ve amended my name as I wasn’t sure why it came up as Kal and not Karl :slight_smile:

6

Hi Sergio

Glad to hear i’m not the only one, hopefully get our issues resolved for a future release

Many thanks

Karl

7

Kathy, you’ll be shocked to hear that I have some thoughts on this. :slight_smile:

Toad offers more formatting options than some of its commercial competitors such as RedGate SQL Prompt or Embarcadero’s Rapid SQL. But there should be a lot more. To see what could be added, I’d encourage you to look at a free tool like Apex SQL Refactor:

Inline image 3

or the $50 SQL Pretty Printer:

Inline image 2

I wants some options that even these don’t have, but if Toad could match these I’d feel a lot better about it. Here are a list of suggestions I sent to the author of SQL Pretty Printer:

  • Common Table Expression: add Keep AS on same line as WITH checkbox

  • Common Table Expression: add parenthesis in a separate line checkbox (like you have for Insert)

  • Common Table Expression: add Indent checkbox (or number of spaces) to apply to the content between the parenthesis

  • Create table: align NULL/NOT NULL just like you currently align data types

  • Create table: add Indent/Align using tabs (for example, data types and NULL/NOT NULL in a CREATE TABLE)

  • Declare: add Align datatype checkbox (like you have in Parameters)

  • Align assignment operators in “SELECT Column1 = 0, Column2 = 1” and “DECLARE @V1 VARCHAR(10) = ‘test’, @V1 INT = 1”

  • Select_keywords_alignOption should apply to keywords under INSERT, DELETE, and UPDATE as well as SELECT, or new separate options for those should be added

  • Add checkbox for: Indent of stored procedure body (we don’t use BEGIN/END on our stored procedures, yet everything after the AS is indented by Pretty Printer anyway)

  • Add checkbox for: Closing parenthesis on a new line; for example:
    INSERT
    INTO Tablename
    (column1
    ,column2
    )
    VALUES (0
    ,1
    );

  • Add checkbox for: Remove unnecessary brackets

  • Add checkbox for: Align ON in joins

    • Add option: Keep EXEC statements on one line if less than n characters

    Add option: Tab equals n spaces

  • Add option: Always use tabs for indentation and alignment

If I don’t choose to format SQL, Toad should leave it alone. There are places where Toad formats SQL no matter what, and that drives me nuts.

8

Hello Mark,

I feel your suggestions are valuable. I went through them and here are my comments (in blue). QP-xyz numbers are cases I created because we should address them. I don’t use Pretty Printer so bear with me if I didn’t catch things you wrote.

  • Common Table Expression: add Keep AS on same line as WITH checkbox
    QP-1931 The current checkbox for “AS/IS on same line” shuld also apply to common table expressions.
  • Add checkbox for: Indent of stored procedure body (we don’t use BEGIN/END on our stored procedures, yet everything after the AS is indented by Pretty Printer anyway)
    QP-1930
  • Add option: Keep EXEC statements on one line if less than n characters should go automatically after addressing EXEC issue
    Current EXEC formatting is broken but will be fixed soon (QP-1897). Then statements which are too wide will automatically be formatted in a way such that they fit nicely in the available space. BTW, this is how Toad formatter works, in contrast to other products which just allow crazy long lines, or which just line wrap.
  • Add option: Tab equals n spaces
    Isn’t that present?
  • If I don’t choose to format SQL, Toad should leave it alone. There are places where Toad formats SQL no matter what, and that drives me nuts.
    QP-1932
  • Align assignment operators in “SELECT Column1 = 0, Column2 = 1” and “DECLARE @V1 VARCHAR(10) = ‘test’, @V1 INT = 1”
    QP-1933
  • Declare: add Align datatype checkbox (like you have in Parameters)
    Isn’t that available?
  • Create table: add Indent/Align using tabs (for example, data types and NULL/NOT NULL in a CREATE TABLE) + Create table: align NULL/NOT NULL just like you currently align data types
    QP-1934
  • Select_keywords_align: Option should apply to keywords under INSERT, DELETE, and UPDATE as well as SELECT, or new separate options for those should be added
    Should be available.
  • Add option: Always use tabs for indentation and alignment
    Available
  • Common Table Expression: add parenthesis in a separate line checkbox (like you have for Insert) + add Indent checkbox (or number of spaces) to apply to the content between the parenthesis
    QP-1935, also working on IN(query)
  • Add checkbox for: Closing parenthesis on a new line; for example:
    INSERT
    INTO Tablename
    (column1
    ,column2
    )
    VALUES (0
    ,1
    );
    You can’t live with the list arrangements we currently offer?
  • Add checkbox for: Remove unnecessary brackets
    Sorry, we do not refactor (change code). After formatting a statement, the formatter will always parse the result and compare the result with the input data to make sure nothing has been deleted or inserted. Should be a guarantee for you.
    Thanks,
    Andre
9

Andre,

I just pasted from an email I sent to the folks that produce SQL Pretty Printer. Some of those suggestions may indeed be for things that Toad already offers.

Thanks for getting involved with this!

  • Mark