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:

• create your own style by using one of the new built-in Redgate styles as a starting point
• share your formatting styles with other SQL Prompt users by storing them in a shared folder
  
• create multiple formatting styles and switch between them
• disable SQL Prompt formatting for certain blocks of code

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:
    

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:

• statements with an INNER JOIN (UserVoice)
• scripts that create or alter a procedure or trigger (UserVoice)
• TRUNCATE statements

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

Format actions for aliases

SQL Prompt 7.3 includes two new format actions:

• Add/remove AS keyword on alias definition for tables and views (UserVoice)
• Apply column alias style (UserVoice)

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:

• CREATE OR ALTER (UserVoice)
• DBCC CLONEDATABASE
• USE HINT query hint

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

• Support for Azure Active Directory Integrated authentication and Azure Active Directory Password authentication (UserVoice)

• The scale and precision of a data type are now shown in tooltips and summary windows (UserVoice):
  
• Improved suggestions when using GRANT with schemas (Forum post)
• OLE DB provider names are now suggested for OPENROWSET and OPENQUERY
• Column names in temp tables using quoted identifiers are now suggested
• DBCC SHOW_STATISTICS now displays options when passing in string arguments (UserVoice)
• Script as INSERT now outputs rowversion/timestamp columns as varbinary
• Experimental feature added to refresh the SSMS IntelliSense (UserVoice)

Fixes

• Find Invalid Objects now reports objects as invalid if a connection to a linked server fails (Forum post)
• Fix for an undocked query window losing focus in SSMS2016 and VS2015 (Forum post)
• Fix for square brackets being incorrectly added around NO_INFOMSGS (Forum post)
• Fix for execute current statement not selecting the whole ALTER TABLE statement (Forum post)
• Fix for incorrectly qualifying a column using the xml "modify" method (Forum post)
• Fix for keyboard focus not changing when switching back to SSMS (Forum post)
• 'd' is no longer used as a keyboard shortcut for deleting snippets in the snippet manager (UserVoice)
• "go" is no longer used as an auto-generated alias name (UserVoice)
• Alt+Shift+F5 is now the keyboard shortcut for "execute current batch" in SSMS2016 and VS2015
• Ctrl+Shift+F5 is now the keyboard shortcut for "execute to cursor" in SSMS2016 and VS2015
• Support ticket 70098: Fix for connection timeout settings not always being respected
• Support ticket 68772: Fix for incorrect suggestion when an alias name is defined as a unicode string literal
• Support ticket 77352: Fix for incorrect precision being displayed on tooltip for DATETIME2 data types
• Support ticket 76234: Fix for incorrect column dependency being displayed
• Support ticket 75896: Fix for incorrect column being used when expanding wildcard
• Support ticket 76423: ambiguous columns are now always qualified with their table/alias inside an ORDER BY clause
• Support ticket 74368: Fix for a semicolon being incorrectly added on an ALTER table statement with a masked column
• Support ticket 73072: Fix for cancel button not responding when using Open in Excel
• Support ticket 72842: Fix for visual glitch when using keyboard navigation on execution warning dialog
• Support ticket 70681: Open in Excel exports Date data types without a time