How to handle SQL formatting

SQL Complete helps beautify SQL statements in a query document, managing the case, line breaks, whitespaces, indenting, wrapping, etc. according to the rules of the current formatting style.

The tool is designed is to transform illegible SQL code into a layout that is easily readable for most users.

The topic includes such sections:

SQL code formatting

To format code in an entire document:

  • On the SQL Complete menu, click Format Document.
  • Right-click the query window and click Format Document.
  • Press Ctrl+K, then Ctrl+D.

To format a code fragment:

1. In a SQL document, select a required code fragment.

2. In the shortcut menu, select Format Selection or press Ctrl+K, then Ctrl+F.

To learn more about the formatting options, see General formatting options.

Use formatting profiles

SQL Complete allows you to format a SQL script by applying different formatting profiles.

To change the formatting profile:

1. In a SQL document, right-click the SQL script, click Active Format Profile, and select the required format.

Change the formatting profile

2. To apply the selected format profile, select the SQL document or the fragment of the code and press Ctrl+K, then Ctrl+F.

In addition, you can create a custom format profile based on the available profiles. For more information about how to create a new format profile, refer to Customize formatting styles with profiles.

Disable formatting for a piece of code

There is a possibility to format an entire SQL document in the editor ignoring the selected code fragments. With noformat and endnoformat tags, you can point out where to stop formatting and where to start formatting again. Formatting can be disabled for a piece of code using one of the following ways:

-- noformat
SELECT [DepartmentID],[Name],[GroupName],[ModifiedDate] FROM [HumanResources].[Department]
-- endnoformat
-- noformat
SELECT [DepartmentID],[Name],[GroupName],[ModifiedDate] FROM [HumanResources].[Department]
/* endnoformat */
/* noformat */
SELECT [DepartmentID],[Name],[GroupName],[ModifiedDate] FROM [HumanResources].[Department]
/* endnoformat */
/* noformat */
SELECT [DepartmentID],[Name],[GroupName],[ModifiedDate] FROM [HumanResources].[Department]
-- endnoformat 

Working with SQL formatting

Outline SQL statements

A statement is outlined in the following cases:

  • It spans over two or more lines
  • It is a CREATE statement

A nested SQL statement (for example, SELECT in CREATE VIEW) is outlined only if it starts from a new line.

A statement is not outlined if it is very short and takes only one line of code. Stored code conditions and cycle operators are not outlined; the same applies to the nested BEGIN … END blocks.

Formatting errors

SQL Complete can detect errors in statements during formatting. Only statements that contain no errors will be formatted.

Once there is a formatting error, you’ll be notified about it by displaying the following message in the Notification window.

Formatting errors

There are two windows where you can view the detected errors. They are:

Output

To call the window, navigate to View > Output.

Output window

In case of a formatting error, you can see the corresponding string in the window as follows:

C:\Users\<Username>\AppData\Local\Temp\<FileName>.sql: Error (<Ch>,<Ln>): Unexpected symbol <Error> (U+004D)

Where

Ch is a position of the symbol in the string.

Ln is a string where the symbol is located.

For example:

Output

Error List

If the IntelliSense feature is enabled in SSMS, then you’ll be able to see the errors in the Error List list. To enable the feature, navigate to Tools > Options, then expand Text Editor and Transact-SQL, and click IntelliSense. Select the Enable IntelliSense and Underline errors checkboxes, and click OK.

Underline errors feature

To access the Error List window, navigate to View > Error List.

Error List

Here you can check all the errors, for example.

Error List window

The errors output behavior can be configured in SQL Complete > Options > Formatting > General > Notifications.

Notifications options

Helpful materials

General formatting options

Get more detailed information about various formatting options and learn when to use them to configure formatting and manage syntax error behavior.

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge SQL Complete for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the SQL Complete in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the SQL Complete for your business, request a demo to see it in action.
Ready to start using dbForge SQL Complete for SQL Server?