Beautify your SQL code using SQL formatter features

In this article, ApexSQL Refactor SQL formatter features will be presented which can be very helpful in creating a well-formatted, clean SQL code.

ApexSQL Refactor is a free SQL formatter and refactoring add-in which can be integrated into SQL Server Management Studio (SSMS) and Visual Studio.

A well-formatted SQL code can save a lot of time in reviewing, especially when it comes to transition projects between teams.

The main problem of each project is that there are no standards of how the SQL code should be formatted and everything is a matter of personal preference. So, in a team and their company, a SQL formatter standard should be set, which all should abide by.

When it comes to creating a SQL formatter standard that will be used (applied) on all SQL codes in your company, the SQL formatter features in ApexSQL Refactor add-in can be very handy.

Creating a SQL formatter profile

ApexSQL Refactor has the feature that can help in creating SQL formatting profiles in which can be implemented SQL formatter standards by using a combination over 200 formatting options.

Under the Options window, create a new SQL formatting profile by clicking the New button. In the Create profile window, type the name of the SQL formatting profile in the Name box. Optionally, enter who creates a SQL formatting profile and purpose of the SQL formatting profile in the Author and Description boxes:

Create SQL formatter profile dialog

Once the initial information is entered for the SQL formatting profile, click the OK button. The newly created profile will be shown under the Profile drop-down box, along with ApexSQL Refactor built-in SQL formatting profiles:

Profile drop down box

ApexSQL Refactor built-in profiles

There are four built-in SQL formatting profiles that came with the installation of ApexSQL Refactor – ApexSQL, Compact, Extended, and MSDN SQL BOL.

More about those formatting profiles and creating and editing user-defined profiles can be found on the How to customize SQL formatting profiles page.

Now, in the newly created SQL formatting profile, the SQL formatting options can be set in a way to meet the company’s SQL formatter standard.

For example, in the company SQL formatter standard, there is a rule in which all SQL keywords like Update, Select, Update, Delete, etc. should be formatted to be in upper case. Or, all system functions like Avg, Substring, etc. should be formatted in lower case.

Under the Capitalization tab of the Options window, choose options that will full fill the company standard:

Capitalization tab

More about SQL formatting options can be found from the links below:

After company’s agreement on the SQL formatter standard in the newly created SQL formatting profile has been achieved, the same profile can be exported and shared with other co-workers in the company by using the Export and Import buttons under the Options window:

Export SQL formatter profile

Now, when the SQL formatting profiles are created and delivered to all co-workers in the company, how can those rules be applied to the SQL code itself?

Applying SQL formatter rules to SQL code

ApexSQL Refactor offers different ways to apply the rules defined in the SQL formatting profile to SQL code written in the SQL scripts.

Individual SQL script formatting

The rules in the SQL formatting profile can be applied to an individual SQL script or on multiple SQL scripts at once.

To apply formatting rules to an individual SQL script, just open that SQL script in SSMS or VS and, from the query editor context menu, choose the desired SQL formatting profile:

Format SQL by profiles command

The same option is available under the ApexSQL Refactor main menu in the host application:

Format SQL by profiles command in the ApexSQL Refactor main menu

Additionally, the SQL formatting profile can be set to be default SQL formatting profile for future formatting of SQL code. To do that, open the Options window, in the Profile drop-down box, choose the profile, click the Set as active button, and then click the Save button to save changes:

Profiles in the Options window

The default SQL formatting profile will have the (Active) mark by its name:

Default SQL formatting profile

Now, the code can be formatter using the Format SQL command from query editor context menu, ApexSQL Refactor toolbar menu, or ApexSQL Refactor main menu in the host application:

Format SQL command

Or simply using the Ctrl+Shift+Alt+F Format SQL shortcut. All four methods will use the default (Active) SQL formatting profile for formatting SQL code in the script.

Formatting SQL scripts in batches

In a situation when, in a company, there are a lot of SQL scripts which code needs to be checked and that code is in a very bad visual shape, like in the image below:

Bed formatted SQL code

Reviewing that code can be a very challenging job. Luckily, ApexSQL Refactor provides a glance solution for formatting multiple SQL scripts at once.

That solution is the Format SQL scripts feature which is located in the add-in main menu, under the Other formatting sub-tab:

Format SQL scripts command

In the list of the Format SQL scripts window, navigate to the folder where the SQL scripts are located, in the Profile drop-down box, choose the SQL formatting profile and click the OK button:

Format SQL scripts window

The Format SQL scripts feature will format all SQL scripts with the desired SQL formatting profile and make the code review of SQL scripts much easier.

Formatting SQL objects in batches

To format multiple SQL objects like stored procedures, functions, etc. with the desired SQL formatting profile at once, the Format SQL object feature can be used.

To initiate this feature, in Object Explorer pane, select a database, right-click and from the context menu, choose the Format SQL objects command:

The Format SQL objects command from Object Explorer pane

The same feature is available via ApexSQL Refactor main menu in the host application:

Format SQL objects command via ApexSQL Refactor main menu

In the Format SQL objects window, all scripts-based objects like triggers, view, functions will be listed:

Format SQL objects window

In the Profile drop-down box, select a desired SQL formatting profile. Then, from the list, choose the objects for formatting and click the Create script button as shown below:

Format SQL objects

A new query window with formatted SQL code will be opened, press the Execute (F5) button to execute the formatted script:

Formatted SQL script

To confirm that the selected objects from the list are formatted with the desired SQL formatting profile, in the list of Format SQL objects window, under the OK column, those objects will be shown with a checkmark icon as shown below:

Formatted SQL objects

CLI SQL formatter

One more useful SQL formatter feature that comes with ApexSQL Refactor is the ability to format SQL scripts using the Command line interfaces (CLI)

To format all SQL scripts within one folder at once with a desired SQL formatting profile and place the formatted SQL script in the same location with the unformatted SQL script, type the following:

ApexSQLRefactor /frs /is:”H:\SQL scripts” /on /pf:”SQL formatting standard”

CLI commands

More about ApexSQL Refactor switches can be found on the ApexSQL Refactor Command Line Interface (CLI) switches page

 

February 3, 2020