December 5th, 2016

Improved SQL formatting

SQL Prompt 7.3 includes a new experimental version of the Format SQL feature:

The formatting styles are now much more customizable. For example, we've added more options for formatting CASE statements, JOINs, CTEs, CREATE TABLE statements, IN values, and more.

 We've also made it easier to:

We're still working on the new formatting feature, and we'd love to know how you think it could be improved.

Get started

To start using the new formatting styles:

  1. In the SQL Prompt options, under Labs, click Experimental features.
  2. Select the Use new formatting styles check box:
     

If you want to switch back to using the old formatting options at any time, clear the Use new formatting styles check box.

Create your own style

To start creating a style, on the SQL Prompt menu, click Edit Formatting Styles.

SQL Prompt 7.3 includes some new built-in Redgate formatting styles that you can use as a starting point for your own style:

To create your own style as a copy of one of the Redgate styles, click Edit style. Alternatively, under Your styles, click + Create a style.

Share your styles

To share styles with your team:

  1. In the Formatting styles window, right-click the path to the style folder, and click Open in Explorer:
  2. Copy the styles you want to share.
    Styles are stored as XML files with the .sqlpromptstylev2 file extension.
  3. Open the shared folder you want to use (a network share or Dropbox folder, for example), and paste the styles into it.
  4. In the Formatting styles window, change the Style folder to the location of your shared folder.

Other SQL Prompt users can then use the same set of styles by changing their style folder to the shared folder.

Use multiple styles

It's now easier to work with multiple formatting styles. The style that will be used when you run Format SQL is the Active style.

To change the active style, in the Formatting styles window, for the style you want to set as active, click  and select Set as active.

Alternatively, in a query window, right-click and use the Active Style menu to select a style:

Disable formatting for blocks of code

You can now disable SQL Prompt formatting for certain blocks of code in a query (UserVoice):

  1. In a query window, select the code you don't want SQL Prompt to format, and click Ctrl to open the actions list.
  2. Type di and select Disable formatting for selected text:

    SQL Prompt encloses the selected code within comments:

This code now won't be formatted when you run Format SQL.

Inline EXEC

SQL Prompt can now inline stored procedures and scalar functions in EXEC statements within the query window (UserVoice).

To do this, right-click on an EXECUTE statement in a script and click Inline EXEC:

You can also inline EXEC sp_executesql (UserVoice). This replaces the dynamic SQL with static SQL.

Execution warnings improvements

You can now choose to show execution warnings for:

To change these settings, go to Options > Code analysis:

Format actions for aliases

SQL Prompt 7.3 includes two new format actions:

To enable these actions, go to Options > Actions:

OUTPUT parameter variables

Variables are now declared automatically for OUTPUT parameters when generating an EXEC statement (UserVoice).

SQL Server 2016 syntax support

SQL Prompt now suggests and formats the following new SQL Server 2016 SP1 syntax:

User account licensing

SQL Prompt now uses Redgate user account licensing, which makes it easier to keep track of the tools you own and move them between different machines.

More information about user account licensing

Other improvements

Fixes