This article will describe how to format SQL code using SQL Server Management Studio (SSMS) native options and how to format SQL code using a third-party SQL formatter tool.
A well-formatted SQL code is easily readable and reviewing such code can be much easier and faster than a non-formatted SQL code.
Format SQL code using SSMS options
SSMS provides a couple of options to format SQL code. Those options are indenting, tab sizing,
make all code in upper or lower case, comment/uncomment selected code, etc.
In SSMS, there are three options for indenting of SQL code:
- None
- Block
- Smart
These three options are located under the Tabs tab of the All Languages sub-tab:
When the None radio button is selected, every time when hitting the Enter key from the keyboard, the cursor in a query editor goes to the beginning of the next line:
When the Block radio button is chosen, the cursor aligns with the previous line when the Enter key is hit:
With the Smart radio button selected, SSMS determines which indenting style will be used:
In the Tab section of the Tabs tab, how many spaces compose a single indentation or tab can be set.
If you want to use the tab characters for tab and indent operations, select the Keep tabs radio button. To use space characters, choose the Insert space radio button. If the Insert space is selected, then in the Tab size or Indent size, enter the number of space characters, each tab or indent represents respectively:
Now, to indent SQL code, in the query editor, select a code and press the Tab key from the keyboard or from the SQL Editor toolbar, click the Indent button:
To unindent SQL code, in the query editor, select the desired code, then on the keyboard, press the Shift+Tab keys combination or, on the SQL Editor toolbar, click the Unindent button:
These two options can also be found in the Advanced sub-menu under the Edit menu:
SSMS provides two options that can covert space to tabs and vice versa. These two options are:
- Tabify Selected Lines – convert space to tabs
- Untabify Selected Lines – convert tabs to spaces
When the Tabify Selected Lines command is used, the spaces in the selected SQL code will be converted to tabs:
As mentioned above; this can be done in the opposite way when the Untabify Selected Lines command is used.
The Delete Horizontal White Space option is used to delete white spaces around the selected SQL code at once:
This option is in the Advanced sub-menu of the Editor menu:
Besides the Delete Horizontal White Space option for removing unnecessary spaces from the SQL code, there is one more option (alternative) which is a combination Alt key with the left mouse click.
In the query editor, press and hold the Alt key, and using the left mouse click select the block of spaces that should be removed, and press the Delete key:
In addition, using this method can add a text multiple times at once. For example, a column alias can be added for each table column in a SQL code. Press and hold the Alt key, positing a cursor next to the first column, click and drag down by holding left mouse click. The light blue vertical line should appear, type a letter and dot (.):
In SSMS there are two format SQL code options for converting SQL code to Upper or Lower case. To make SQL code to be in Upper case, in a query editor, select the desired SQL code and, from the Advanced sub-menu, pick the Make Uppercase command:
Use the Make Lowercase command to convert to SQL code in lower case.
Word wrapping is another option available in SSMS for formatting SQL code and it is in the General tab of All Languages under the Test Editor tab:
When the Word wrap and Show visual glyphs for word wrap options are checked, the lengthy T-SQL lines in a query editor will be wrapped with indicators where the wrapping is applied. Also, the horizontal scroll bar will be eliminated:
Format SQL code using third-party SQL formatter tool
ApexSQL Refactor is SSMS and Visual Studio add-in for format SQL code. ApexSQL Refactor provides over 200 formatting options that can be used to beautify a SQL code. In this part of the article, how to create a formatting profile and how to use some of the ApexSQL Refactor formatting options will be explained. Also, it will be shown how to format SQL code in a query editor using formatting profiles.
Besides formatting SQL code in a query editor directly, ApexSQL Refactor has features that can format code base SQL objects like stored procedures, functions, views and SQL scripts in batches. More about those formatting features can be found on the Beautify your SQL code using SQL formatter features page.
ApexSQL Refactor comes with four built-in formatting profiles that can be used out of the box to format SQL code.
Built-in ApexSQL Refactor SQL formatting profiles:
- ApexSQL – this profile provides the best formatting style by ApexSQL opinion
- Compact – all spacing options are unchecked, and indentions options set to 0 (zero) space for a query where the SQL code looks dense
- Extended – Spaces and empty lines are added before/after every statement. This profile is opposite of the Compact profile
- MSDN SQL BOL – mimic the style from the MSDN resource site
These four formatting profiles among the rest of ApexSQL Refactor formatting options are located under the Options window:
Besides these four built-ins, a custom SQL formatting profile can be created. In the Options window, click the New button and, in the Create profile window, enter a name for the profile, optionally enter the author of a formatting profile and description:
More about SQL formatting profiles can be found on the How to customize SQL formatting profiles page.
After creating a SQL formatting profile, you can start a selection of options in the Options window to format SQL code:
For example, under the Capitalization tab, formatting all SQL keywords in upper case and all system functions in the lower case can be set:
All options for formatting tables in a query editor are located under the Tables sub-tab of the Lists tab:
And all related options for manipulating of columns are located under the Columns sub-tab:
Additional information about SQL formatting options can be found on 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
Format SQL code
There are a couple of ways to format a SQL code using the custom created formatting profile.
In a query editor, paste some SQL code, go to the ApexSQL Refactor main menu, under the Format SQL by profile sub-menu, choose your newly created formatting profile:
The second way to format a SQL code using custom created SQL formatting profile is via query editor context menu. Right-click in a query editor and, from the context menu, under the Format SQL by profile sub-menu, select the custom created SQL formatting profile:
Additionally, the custom created SQL formatting profile can be set as default SQL formatting profile for the future format SQL code. Open the Options window, in the Profile drop-down box, select the desired SQL formatting profile, click the Set as active button and click the Save button:
Now, when this is set, you have additional SQL formatting command that can be initiated from a query editor, ApexSQL Refactor main menu, ApexSQL Refactor toolbar or using the Ctrl+Shift+Alt+F shortcut:
February 20, 2020
