Marko Zivkovic

Manage SQL code formatting using SQL formatter options

October 24, 2018 by

This article will provide a review of SQL code formatting using the SQL formatter options in SSMS

Nobody likes to read a large amount of text, even when it’s just a plain one. When reading SQL script where there is a large amount of non-formatted SQL code, the problem becomes even bigger.

SQL code can have different SQL formatting styles, which could make a job either easier or more difficult. It can make code difficult to unscramble and understand. A clean SQL can be read faster than an inconsistently written SQL; SQL reviewing and troubleshooting are more efficient; joint development efforts are more effective; handing off projects from one team to another is easier.

How to make code readable so that it is enough just to skim through the code and get the general idea.

It’s the SQL formatter that makes the difference.

All SQL code can be formatted manually, which is very time consuming but 3rd party SQL formatter tools do exist and can be used to help formatting quickly and efficiently.

This article will guide you through some SQL formatter options via SSMS and using 3rd party SQL formatting tool such is ApexSQL Refactor to format code.

So, let’s first start with SQL formatter options to format code and then ApexSQL Refactor add-in to do the same thing and more.

Indenting

In SQL Server, three are three options for indenting of SQL code.

To select desired indenting options, go to the SSMS Tools menu and, from the context menu, choose the Options command:

SSMS options

In the Options window, click the Text Editor tab and under the All Languages sub-tab, select the Tabs tab:

SSMS indenting options

  • None

When this option is selected, in a query editor, when the enter key from the keyboard is pressed, the cursor goes to the beginning of the next line:

SSMS indenting options - none

  • Block

When this option is selected, in a query editor, when press the enter key, the cursor aligns the next line with the previous line:

SSMS indenting options - block

  • Smart

By default, this option is used. It determines the appropriate indenting style to use.

In the Tabs tab, it can be specified how many spaces compose a single indentation or tab, and whether the Editor uses tabs or space characters when indenting.

If tab characters for indent and tab operations is needed, select the Keep tabs radio button:

SSMS text editor options - tabs

But if want to use space characters for indent and tab operations, choose the Insert spaces radio button:

SSMS text editor options - insert spaces

Under the Tab size and Indent size boxes, enter the number of space characters, each tab or indent represents:

SSMS text editor options - tabs, tab size

To indent SQL code, in a query editor, select the code that wants to indent and press the Tab key from the keyboard or, from the SQL Editor toolbar, press the Indent button:

SSMS text editor options - indenting code from the editor

For unindenting code press the Shift+Tab keys or use the Unindent button from the SQL Editor toolbar:

https://s33046.pcdn.co/wp-content/uploads/2018/10/word-image-224.png

The same options for Indent and unindent code are available under the Advanced sub-menu of the Edit menu:

SSMS text editor options - indenting menu options

To convert spaces to tabs in SQL code and vice versa in a query editor, select the spaces that want to convert in tabs, go to the Edit menu and under the Advanced sub-menu, choose the Tabify Selected Lines command for converting white space to tabs and choose the Untabify Selected Lines command to convert tabs to spaces:

SSMS text editor options - tabify options

To see if spaces in a code are converted to tabs and vice versa, go to the Edit main and, under Advanced sub-menu, choose the View Whit Space command:

SSMS text editor options - white space

For example, if the Tabify Selected Lines command in the code to convert spaces in tabs is used, the following code:

SSMS text editor SQL layout options - spaces

will look like this:

SSMS text editor SQL layout options - spaces converted to tabs

And when the Untabify Selected Lines command is used to convert tabs into spaces, the SQL code will look like this:

SSMS text editor SQL layout options - tabs converted to spaces

Whenever the data migration from any old systems to new/upgraded systems, using different formats like text files, csv, excel or some other format to insert data into SQL Server, the additional white spaces can be added, like in the example below:

Luckily, these white spaces can be easily removed using the Delete Horizontal White Space option. In a SQL editor, select the code and under the Advanced sub-menu of the Editor menu, choose the Delete Horizontal White Space command:

SSMS text editor SQL layout options - delete horizontal white space

The Delete Horizontal White Space removes all spaces for the selected SQL code:

SSMS text editor SQL layout options - spaces - Delete Horizontal White Space removes all spaces for the selected SQL code

Another way to get rid of unnecessary white spaces is to hold the Alt key and left mouse click, select the block of white spaces:

After selecting press the Delete key to remove those spaces:

Alternately, with this type of selection, a text can be added multiple times at once. To clarify, to add a table alias to columns in this case the a alias:

Press the Alt key on the keyboard and left click on the mouse, click and drag to select the desired block before columns, as you may notice a very light vertical blue line will appear, after that just type the latter a with the dot (.) and will be placed in front of the all columns:

Converting SQL code to Upper or Lower case

To convert code to be in upper case in a query editor, select a code that wants to convert and under, the Advanced sub-menu, choose the Make Uppercase command or use the Ctrl+Shift+U shortcut:

SSMS SQL layout text editor options - Make uppercase

The selected code will be formatted like this:

SSMS SQL layout text editor options - Make uppercase results

To covert code to be in lower case, select desired code and use the Make Lowercase command or Ctrl+Shift+L shortcut:

SSMS SQL layout text editor options - Make lowercase

Wrapping SQL code

When T-SQL scripts were created with lengthy T-SQL lines, to review code in such T-SQL scripts either use the horizontal scroll bar or create line breaks at various points to make the line readable and rid of the horizontal scrollbar. Scrolling or having to break the lines of code reviewing of code can be time consuming.

In SQL Server Management Studio (SSMS) there is an option for word wrapping.

From the Tools menu, select the Options command:

SSMS SQL layout text editor options - wrapping

In the Options window, expand the Text Editor tab and under the General tab of All Languages, the Word wrap option is located:

SSMS SQL layout text editor options - Word wrap

By default, this option is unchecked. When the Word wrap option is checked the Show visual glyphs for word wrap option becomes available:

SSMS SQL layout text editor options - Word wrap - show visual glyphs for word wrap

The Show visual glyphs for word wrap option placed marks on every line where the word wrapping is applied.

In the example below a SQL code is shown without Word wrap and Show visual glyphs for word wrap options and with these options checked.

Code without Word wrap and Show visual glyphs for word wrap options checked:

Code without Word wrap and Show visual glyphs for word wrap options checked

Code with Word wrap and Show visual glyphs for word wrap options checked:

Code with Word wrap and Show visual glyphs for word wrap options checked

As it can be seen, the horizontal scrollbar is gone and places, where the word wrap have been applied, are marked.

3rd party SQL formatter tool

This part of article will explain in base what can be archived with ApexSQL Refactor SQL formatter tool.

First that can be noticed when install ApexSQL Refactor is that immediately can be used to format SQL code by using one of four options in SQL formatting profiles:

  • ApexSQL – contains what ApexSQL determined that is a good SQL formatter standard
  • Compact – all options for spacing are unchecked, indentions options set to 0 (zero) space, removed empty lines for a query where the SQL code looks dense
  • Extended – add spaces, empty lines before/after every statement. In base, this profile is opposite of the Compact profile
  • MSDN SQL BOL – emulates the style that is used in the MSDN resource site

SQL layout style - MSDN SQL BOL – emulates the style that is used in the MSDN resource site

If those doesn’t meet your needs a new SQL formatting profile can be created simply by clicking the New button on the Options window and fill in information about the name of a new SQL formatter profile and press the OK button:

Create a custom SQL formatter profile

Under the Formatting tab can be set indentation and word wrap text and avoiding horizontal scrollbar in a query editor by checking the Wrap lines longer than checkbox and set how many characters a line will be long:

SQL formatting options - Indentation

In the Capitalization tab can be set a various capitalization rule for the SQL keywords, Data types, Identifiers, System functions, Variables separately:

SQL formatting options - Capitalization

Even better all changes that are made when choosing some of the options are immediately visible in the preview section of the Options window:

SQL formatter options - column lists

Another advance of using ApexSQL Refactor is his ability to share custom created profiles and make it accessible to others, by clicking the Export button:

SQL formatter options - exporting profiles

Co-workers can import shared SQL formatting profile and multiple apply it on theirs SQL objects (stored procedures, functions, views) or SQL scripts by using Format SQL objects feature for SQL objects or Format SQL scripts feature for SQL scripts at once.

More about ApexSQL Refactor SQL formatter features and options can be found on the Top things you need in a SQL formatter tool page.


Marko Zivkovic
SQL Server Management Studio (SSMS), T-SQL

About Marko Zivkovic

Marko aka “Zivko” is a senior software analyst from Nis, Serbia focusing on SQL Server and MySQL as well as client technologies like SSMS, Visual Studio, and VSCode. He has extensive experience with quality assurance, issue escalation/resolution, and product evangelism. He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more. In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music. See more about Marko at LinkedIn View all posts by Marko Zivkovic

168 Views