Often when you write a large piece of SQL code, it eventually becomes a bit disorganized, or when a big team of developers is working on one project, the code differs between individual developers, the code formatting throughout the project gets inconsistent and hard to read. Let us learn about how to Beautify SQL Code with dbForge SQL Complete.
In this article, we will talk about how to keep your code clean and well-formatted no matter how many developers were involved in writing it. The article also discusses the way to introduce a unified SQL code formatting standard for across a team so that the code could be easily reviewed and edited.
How to make SQL code readable
In a code review process, a reviewer can encounter various coding styles used by individual developers. This can complicate the review process and the reviewer will have to scroll up and down the code many times to completely review the code.
In this case, the code needs to be properly formatted and consistent throughout the file. One of the best SQL code formatter tools I know is dbForge SQL Complete – a smart add-in for Microsoft Visual Studio and SSMS. The tool provides an excellent functionality that can make your code look clean and pretty.
Now, let’s see the tool in action.
The following piece of unformatted messy code is taken from a production environment. You can see that the text is badly formatted lacking indentations and spacing; keywords are presented in a different case throughout the code. In other words, it requires thorough reworking.
When you look at this code, you really don’t know where to start. Luckily, dbForge SQL Complete offers SQL Code Formatting feature. Simply use a well-known key combination (Ctrl+K+D) to quickly make the code properly formatted. The result looks fantastic:
How to customize formatting profiles
The above formatting is a standard formatting pattern used by dbForge SQL Complete. However, if you want to have the code formatted in your own particular way that meets your preferences, then you can manually adjust formatting options. Simply go to the SQL Complete menu and click Options. In the left pane, click Formatting and then click Profiles. You will see three built-in profiles: Default, Profile 1, and Profile 2, each having different formatting options to choose from.
If none of them works for you, you can add your own formatting profile and customize it as you desire. To do this, click New Profile, specify the name and the profile to copy settings from, and then click OK. After that, select the new profile and click Edit Profile.
The Edit Formatting Profile wizard provides a huge number of flexible customizations settings. The wizard is divided into two panes. The left pane presents a tree of features that can be customized individually. When you select a feature, the right pane shows a code sample where different properties for this feature can be set.
For example, you want to introduce the following formatting options:
- put each statement on a separate line;
- add a line break after WHERE statement;
- add a small indent to conditions.
To achieve this, complete the following actions:
- Navigate to the Stacked List option, and clear the Place comma before item in stacked list item;
- Navigate to the Subquery Clear the Use same formatting as in SELECT item and select Line break after opening brace;
- Navigate to the WHERE option and select the Line break after WHERE item;
- Navigate to the FROM option and select the Line break after FROM
Once you are all set, click OK to save the settings. Now, format the code once again and assess the output:
How to share code formatting settings
Now the code looks well-formatted and you can easily read it and edit it.
dbForge SQL Complete allows you to distribute code formatting settings among all members of your team. This way, you can standardize the code appearance so that developers working on a single project could easily read and edit any part of the project’s code.
The described features of dbForge SQL Complete are very helpful when you want your code to be properly formatted and readable. It also provides flexible means for the developers to share code formatting profiles across their team.
Besides, dbForge SQL Complete offers Code Completion, Code Snippets, SQL Refactoring, and a bunch of other useful features that boost up SQL code writing and productivity.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I know if one of my developers brought me either of these samples of code, it would be sent back for modification. Unless you have strict coding standards, you’ll end up with code smell and using this tool appears to only speed up the process.
Thanks for sharing your thoughts @Ginger.
I absolutely love this tool – it helps me (as a developer) comply with our DBA’s preferred formatting standards for stored procedures.
Thanks for sharing.