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 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:
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:
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:
More about SQL formatting options can be found from the links below:
- Rules of SQL formatting – Commas and spacing in T-SQL
- Rules of SQL formatting – Regular and delimited T-SQL identifiers
- Rules of SQL formatting – SQL code commenting
- Rules of SQL formatting– SQL naming conventions and capitalization rules
- Rules of SQL formatting – Terminating SQL statements with semicolons
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:
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:
The same option is available under the ApexSQL Refactor main menu in the host application:
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:
The default SQL formatting profile will have the (Active) mark by its name:
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:
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:
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:
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:
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 same feature is available via ApexSQL Refactor main menu in the host application:
In the Format SQL objects window, all scripts-based objects like triggers, view, functions will be listed:
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:
A new query window with formatted SQL code will be opened, press the Execute (F5) button to execute the formatted 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:
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”
More about ApexSQL Refactor switches can be found on the ApexSQL Refactor Command Line Interface (CLI) switches page
February 3, 2020
