How to format SQL code in SQL Server Management Studio

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:

SSMS Indenting options

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:

None indenting option in query editor

When the Block radio button is chosen, the cursor aligns with the previous line when the Enter key is hit:

Block indenting option in query editor

With the Smart radio button selected, SSMS determines which indenting style will be used:

Smart indenting option in query editor

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:

SSMS text editor options - Indent size, Tab size

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:

The Indent space option under the SQL Editor toolbar

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:

The Unindent space option under the SQL Editor toolbar

These two options can also be found in the Advanced sub-menu under the Edit menu:

SSMS text editor options - indenting menu options

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

SSMS text editor options - Tabify Selected Lines and Untabify Selected Lines options

When the Tabify Selected Lines command is used, the spaces in the selected SQL code will be converted to tabs:

Covert spaces into tabs with Tabify Selected Lines command

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:

Format SQL code using the Delete Horizontal White Space command

This option is in the Advanced sub-menu of the Editor menu:

SSMS text editor SQL layout options - delete horizontal white space command

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:

Remove unnecessary space from SQL code using Alt + left mouse click combination

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 (.):

Add a text multiple times at once

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:

SSMS SQL layout text editor options - Make uppercase

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:

SSMS SQL layout text editor options - Word wrap

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:

Wrapping lengthy T-SQL lines using the Word wrap and Show visual glyphs for word wrap options

Format SQL code using third-party SQL formatter tool

ApexSQL Refactor is a free 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:

Options window with four built-in SQL formatting profile

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:

Custom SQL formatting profile

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:

Selecting adequate formatting options

For example, under the Capitalization tab, formatting all SQL keywords in upper case and all system functions in the lower case can be set:

Capitalization tab

All options for formatting tables in a query editor are located under the Tables sub-tab of the Lists tab:

Tables tab

And all related options for manipulating of columns are located under the Columns sub-tab:

Columns tab

Additional information about SQL formatting options can be found on the links below:

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:

Format SQL by profile sub-menu

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:

Context menu Format SQL by profile option

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:

Set as active SQL formatting profile

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:

Format SQL command

 

February 20, 2020