SQL SERVER – Free SQL Server Formatting Add-in

This article describes the ways in which you can format SQL code using ApexSQL Refactor features and options. Let us learn about SQL Server Formatting Add-in.

ApexSQL Refactor is a free add-in with over 200 formatting options and nearly 15 code refactors. You can qualify object names, expends wildcard, encapsulate SQL code, renames SQL database objects and parameters without breaking dependencies. The add-in can be integrated into Microsoft SQL Server Management Studio and Visual Studio.

To format SQL code inside Microsoft SQL Server Management Studio or Visual Studio select code in the query window and from the Format SQL sub-menu, choose one of the four build-in formatting profile:

Another way to access the formatting profiles is via ApexSQL Toolbar:

You can modify built-in formatting profiles or created your on user defined formatting profile using numerous of options from Options form, which is located under the ApexSQL Refactor menu. All options are grouped in tabs and sub-tabs.

Formatting SQL options

In the Formatting tab, can be set the indentation by using spaces or tabs. Aligns identifiers in the data statements using Smart indent option. You can limit number of characters that applies per line by adjust wrapping option on the desired length. Additionally, you can add spaces around assignment operator or add spaces before and after the commas. The Empty lines section contains options for manipulating  empty lines, you can remove or add empty lines before/after comments, remove/add empty lines before each statement, change several empty lines into one empty line:

In the Capitalization tab, capitalization style can be set for SQL keywords, data types, identifiers, system functions and variables using options from combo box such upper case, lower case, upper/lower camel case, and proper case:

In the Comments tab, comments can be set to suit your needs, you can add an empty line before and after block comments or add borders above/below block comments using any separates such (*, -, +, /). Additionally, you can enforce all comment type to change into block/line comments or simply remove all blocks/line comments:

In the Statements tab, a various of options can be set for data statements, nested selections, object definitions, alias.  A data statements can be set to be formatted only if are longer than specified number of charters. Under the Format data statements FORM, WHERE and SET clause can be placed in a new line, aligned or indented.  For all nested selects, opening and closing parentheses can be moved to a new line, align with keyword or indented. Additional closing parentheses can be aligned with opening parentheses. Also code within parentheses can move to a new line, placed at the same position after the last character in the preceding line, align with preceding keyword or indented.

Aliases names can be aligned. The AS keyword can be set for all aliases, placed on a new line aligned with the preceding keyword or indented:

Under the Lists tab are sub-tabs in which can be set up formatting style for tables, columns, values, where in values, assignments, variables, parameters.

In the Tables sub-tab, place the table item list can be set to a new table, align table item list with keyword or indented them. Additionally, you can place each item from the list on a new line and place a comma before or after each item:

In the Columns sub-tab, column list can be set by place a list on a new line, align column list, indented. Also, you can place each item on a new line with a comma before or after each column item. The opening parentheses can be moved to a new line, placed opening parentheses at the same position after the last character in the preceding line, align with keyword or indented. The same formatting can be achieved with closing parentheses. Additionally, closing parentheses can be aligned with opening parentheses. In this tab you can add spaces inside/outside parenthesis:

In the Values sub-tab, value lists can be set up by placing a value list on a new line, align with keyword, indented. Each value from a list can be placed on a new line with a comma before/after each value. Opening/closing parentheses can be placed to a new line, align with keyword, indented. Opening parentheses can be placed on a new line after the last character in the preceding line and closing parentheses can be aligned with opening parentheses. Spaces inside/outside parentheses can be added:

At the Where in values sub–tab, WHERE IN value list can be placed on a new line, align with keyword, indent value list by spaces/tabs from beginning of the statement. Each item can be placed to a new line with a comma before/after each item. You can manipulate with opening and closing parentheses by move them to a new line, align with keyword or indented:

Under the Assignments sub-tab, assignment list can be set on a new line, align with keyword, indented, and placed each item from the list on a new line and placed comma before/after each item.  You can place each opening/closing parentheses to a new line, align with keyword, align with the previous line, indented. Spaces inside/outside parentheses can be set:

In the Variables sub-tab, variables list can be set on a new line, aligned or indented. Each value can be placed on a new line with a comma before/after each value:

In the Parameters sub-tab, parameter lists can be set on a new line, aligned, indented. Each parameter can be set on a new line and separated with a comma before/after each parameter. Parentheses can be set to a new line, aligned or indented. Inside/outside each parentheses can be added additional space:

Under the Expressions tab, in the Arithmetic, Comparison, Logical sub-tabs numerous options can be set to format arithmetic, comparison, logical operations. You can set to format operations only if are longer than specified number of charters. An additional operation can be set to a new line, show operations on multiple lines, add spaces around operators. Opening /closing parentheses can be moved to a new line, aligned, indented, added space inside/outside the parenthesis:

In the Join tab, can be set to format only JOIN statement longer than specified number of characters. You can placed first table on a new line, align with previous keyword or indented. The JOIN keyword can be placed in a new line, align with previous keyword or with a first table, indented. All joined object can be placed on a new line. The ON keyword can be moved to a new line, aligned with previous keyword, JOIN keyword or align with first table, indented. Each nested join can be placed on a separate line, aligned with previous keyword, previous JOIN keyword, or aligned with first table, indented:

Under the Flow control tab, WHEN, THEN, ELSE condition keywords can be set on a new line or indented.  You can set BEGIN and END keywords to be used in every IF statement and stored procedures. Additional, you can indent BEGIN and END blocks, the code within BEGIN and END blocks or condition body statement if there are no BEGIN and END block:

In the Obfuscation tab, can set to SQL code make less readable by using one of the wrapping options, randomize keywords or remove comments:

Impact preview

All changes that made while clicking on/off options under tabs can be seen in code editor (e.g. removing/adding lines, spaces, character changes) that located on the right side of the Options dialog window. For example the image below shows code before and after using the “Remove all block comments” options under the Comments tab:

Before: After:

Another example is formatting keywords from UPPER CASE to Proper Case under the Capitalization tab:

Before: After:

Also, code editor shows impact of all changes when switching from one profile to another. In the images below is an example of how the code looks when it is formatted with the Default profile and how the code will looks when the MSDN SQL BOL profile from the Profile drop-down list is selected:

Default profile:

Code, and all changes when a switch from the Default to MSDN SQL BOL profile:

Creating user defined formatting profiles

As we mentioned at the beginning of this article you can create your own user defined formatting profile and use it to format SQL codes or scripts rather than using build-in formatting profiles. To create a new profile go to the Options dialog and click the New button. In the New profile form enters the profile name and click the OK button:

A new profile will automatically appear in the Profiles drop-down box. Now from the tabs in the Options dialog you can choose formatting options that suit your or your company’s needs. To preserved settings, click the OK button on the Options dialog window.

The benefit of creating user defined formatting profile is that they preserved your preferred formatting options, makes code easier to read and maintenance, profile can be easily edited and modified, avoid formatting conflict with users/others.

Export/Import profiles

Using the Export and Import button from the Options dialog window, you can export user defined or build-in profile to your local machine or import them to another machine:

Format SQL objects

To format one or more database objects with the specified formatting profile, without having to script them first, you can use the Format SQL objects.

First select database from which you want to format objects and from ApexSQL Refactor menu, choose the Format SQL objects command. From the Profile drop-down list, select a formatting profile and from the list choose objects (e.g. view, stored procedure, function, and trigger) that you want to format:

Before formatting additionally you can compare a SQL object’s script as it now and how it will look after formatting. Right click on the SQL object from the list and click the Compare button:

Format SQL scripts

To format SQL scripts outside SQL Server Management Studio and Visual Studio, from ApexSQL menu, choose the Format SQL scripts feature, from there you can select single or multiple SQL files/folders and format them with specified formatting build-in or user define formatting profile. Additionally, you can overwrite an old SQL file with new one or create a new SQL file and preserved the old one:

ApexSQL Refactor has various types of features for code refactoring.

Reference: Pinal Dave (https://blog.sqlauthority.com)