Over a million developers have joined DZone.

Tidy SQL With dbForge SQL Complete

Take a look at dbForge, a nifty tool that can format SQL code to your (more readable) choosing.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

An article written by Andrey Langovoy

In this article, we will talk about how to keep SQL code clean and highly readable. We will also discuss how to stop suffering from messy and unformatted code. In addition, we will talk about how to force all team members to adhere to generally accepted coding standards with no effort.

Increasing Code Readability

There are cases when SQL developers debug code or perform enhancements on existing or legacy code. For instance, while initiating a code review process, developers deal with different coding styles and techniques. Some have coding standards and are mostly consistent in the way all scripts are written. Some have no coding standards and write code with their own sweet will. This requires a reviewer scrolling horizontally a few times to read the code entirely.

It is much easier to understand the flow of the code when it is well-formatted and has a good degree of consistency.

When the code looks unreadable, I would prefer to use some sort of SQL formatting tool.

My favorite tool to format messy code is dbForge SQL Complete — an excellent add-on for Microsoft Visual Studio and SSMS. Along with automatic code formatting features, SQL Complete provides outstanding IntelliSense-like functionality, which makes your work much more productive compared to manual coding.

Let’s have a look at how the tool formats code.

The following image demonstrates the real (taken from the production) example of the unformatted SQL code. Besides the formatting problem, you might notice that the keywords appear in the various case across the text.

messy-sql-before


Well, it's not so easy to understand what the code does.

Thankfully, SQL Complete gives us the ability to format messy SQL code by using a well-known shortcut. Let’s press CTRL+K+D and look at the output.

messy-sql-after


Managing Code Formatting

Now it’s looking much better, isn’t it? The next step is to decide whether the standard formatting output meets your requirements. If not, you can easily adjust it by tweaking formatting options. To do so, go to the SQL Complete menu and click Options. Navigate to Formatting Profiles.

There are three predefined formatting profiles that provide you with various formatting options. Try to select various profiles and see the result.

In case it doesn’t work for you, it’s easy to create a new profile and customize it.

formattting_profile_new


Once you’ve created the new profile, you can select it and edit.

Let’s customize the new formatting profile to add more structure to the existing piece of SQL code to enhance its readability.

The Edit Formatting Profile wizard provides you with more than a hundred formatting options that cover any needs. You can use the search functionality to find a required setting in the tree. Additionally, you have a quick preview pane that adds more convenience to the process.

Edit-FromattingProfile


First of all, let’s place each statement on a separate line, add the line break after WHERE, and indent conditions slightly.

Let’s uncheck the Place comma before the item in stacked list option. For the subquery, clear the "use same formatting as in SELECT" checkbox, and select Line break after opening brace. Also, add line breaks after WHERE and FROM.

Once you finish the tweaking process, format the code again to assess the result.

SQL-Formatting-Final-Result


When SQL code is well-formatted, you do not need to spend too much effort reading and analyzing it.

Adhering to Coding Standards

The formatting profile is nothing but a simple XML file. You can share the file within your team. This way, each SQL developer will have an ability to commit SQL code to the project that is formatted according to accepted rules.

Summary

As you can see, dbForge SQL Complete is a simple and intuitive tool that provides advanced code formatting features.  Also, the tool provides you with a number of features like Code SnippetsAutomatic Code Completion, and SQL Refactoring that increase your productivity while developing SQL code.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
sql server 2016 ,sql server ,intellisense ,refactoring ,tool ,tools

Published at DZone with permission of Jordan Sanders. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}