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:
In the Options window, click the Text Editor tab and under the All Languages sub-tab, select the Tabs tab:
- 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:
- 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:
- 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:
But if want to use space characters for indent and tab operations, choose the Insert spaces radio button:
Under the Tab size and Indent size boxes, enter the number of space characters, each tab or indent represents:
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:
For unindenting code press the Shift+Tab keys or use the Unindent button from the SQL Editor toolbar:
The same options for Indent and unindent code are available under the Advanced sub-menu of the Edit menu:
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:
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:
For example, if the Tabify Selected Lines command in the code to convert spaces in tabs is used, the following code:
will look like this:
And when the Untabify Selected Lines command is used to convert tabs into spaces, the SQL code will look like this:
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:
The 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:
The selected code will be formatted like this:
To covert code to be in lower case, select desired code and use the Make Lowercase command or Ctrl+Shift+L shortcut:
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:
In the Options window, expand the Text Editor tab and under the General tab of All Languages, the Word wrap option is located:
By default, this option is unchecked. When the Word wrap option is checked the Show visual glyphs for word wrap option becomes available:
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 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
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:
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:
In the Capitalization tab can be set a various capitalization rule for the SQL keywords, Data types, Identifiers, System functions, Variables separately:
Even better all changes that are made when choosing some of the options are immediately visible in the preview section of the Options window:
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:
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.
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
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019