One of the primary features of dbForge SQL Complete—a top-tier add-in for SSMS and Visual Studio—is code formatting, which helps beautify SQL code and make it consistent and easy to read, understand, review, and share across teams.
The built-in formatter makes this possible with a number of options, including wizard-aided bulk formatting, noformat tags, predefined profiles, database identifier case synchronization, word recognition in CamelCase identifiers, and automation. The result becomes evident with the increased efficiency of your code reviews, faster troubleshooting, and overall improved team productivity. What is more, you can set up your own custom SQL coding standards and adopt them in your company.
In this article, we would like to explore one of the formatter’s capabilities; namely, we will take a predefined formatting style (called “profile” in SQL Complete), introduce several changes to it and make it a new custom profile. After that, we will compare these two styles and see how easy it is to spot the differences we have applied.
Creating a custom formatting profile in SSMS using SQL Complete
Our example will involve a native stored procedure, taken from AdventureWorks2019; and first off, we will generate an ALTER script for it in SSMS. To do that, in Object Explorer, we right-click a procedure (say, dbo.uspGetBillOfMaterials), and then we select Script Stored Procedure as > ALTER To > New Query Editor Window from the shortcut menu.
After we do it, the result is as follows:
Note the visual inconvenience of the SELECT and WITH statements, where the parameters go one by one in a single row. Now, if we select Format Document from the SQL Complete menu, the Default formatting profile will be applied, and we will see that the parameters in the SELECT statement are now stacked:
Looks better, doesn’t it? Now we want our WITH clause to look the same way. For this purpose, we will modify the current Default profile with additional settings to create a Custom profile.
To do that, we go to the SQL Complete menu > Options > Formatting > Profiles > Create New. There, we enter the name for our profile, select the format to copy the initial settings from, and specify a path to where our profile will be stored in an XML file. By default, the suggested path is C:\Users\username\AppData\Roaming\Devart\dbForge SQL Complete\FormatProfiles\ProfileName.xml. In our case, the “username” will be “jordansanders”, and our “ProfileName” will be “Custom”.
Once our Custom profile appears on the list, we select it, click Open Selected, go to PROCEDURE/FUNCTION > Parameters, select the Stack parameters list checkbox, and additionally select simple list:
Then we click Save to apply our changes, go back to the procedure, and apply the newly created Custom profile to it by selecting Format Document from the SQL Complete menu. This makes our SELECT statement look just as great:
That’s it! Now that you have seen how it works, let’s compare our profiles.
Comparing Default and Custom profiles
The simplest way to do this is to compare the respective profile files in Code Compare, a Devart application that makes code comparison easy and convenient.
First, we must retrieve the profile files. As we can remember, the Custom profile was previously saved to C:\Users\jordansanders\AppData\Roaming\Devart\dbForge SQL Complete\FormatProfiles\ as Custom.xml.
Since the predefined files are not stored in that folder, we can go back to the SQL Complete menu > Options > Formatting > Profiles > Create New to create a new profile, copy the Default settings without changes, and here we go—we’ve got the second XML file for comparison.
Now let’s open Code Compare and upload our files via the File menu (alternatively, you can simply drag-and-drop them onto the respective sections):
The comparison results are delivered instantly, and now we can see where we selected the Stack parameters list checkbox:
The second difference shows where we additionally selected the simple list mode, which was the first option on the list (while the default mode, single line, was the third option):
That’s it! As you can see, it is very easy to create and modify custom formatting profiles in SQL Complete, and then compare them in Code Compare. All in all, SQL Complete is an absolute marvel when it comes to formatting capabilities. And you don’t have to believe our words; just download your FREE 14-day trial of SQL Complete and check it in action today!
