october 13, 2023
Automation tools for SQL code styling
This post is going to list free SQL code formatting tools. The priority of the quest was to find add-ons to SQL Server Management Studio (SSMS) or sites that could provide it.
The T-SQL procedures were used for testing. The purpose of this work is to bring to a common format T-SQL code of employees.
After analyzing the market, only three sites out of a large variety handled correct SQL formatting:
1. Instant SQL Formatter
It is a free version of the commercial tool SQL Pretty Printer tool.
Link: https://www.dpriver.com/pp/sqlformat.htm
2. red-gate.com/website/sql-formatter (SQL Prompt)
This formatter is based on the fee-based SQL Prompt tool, which is a multifunctional add-on for SSMS and VS.
Link: https://www.red-gate.com/website/sql-formatter
3. Poor SQL (Poor Man’s T-SQL Formatter)
Open Source solution, has different variations (site, library, application, add-ons), many repositories on github use it as a framework for their formatters.
Link: https://poorsql.com/
Choosing from free add-ons and considering the ease of installation and test examples, Poor Man’s T-SQL Formatter was the best as an add-on for SSMS.
You could use Format T-SQL Code by installing this add-on and expanding the «Tools» toolbar and customizing the T-SQL Formatting Options.
The formatting settings window looks like this:
To install Poor Man’s T-SQL Formatter, I needed:
1) Download the installation file of the add-on for SSMS and start the installation;
2) Update the .NET Framework 2.0;
3) Add a dependency to the configuration file (Ssms.exe.config).
To add a dependency to the SSMS configuration file:
1) Find the configuration file, which is located in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE;
2) By right-clicking on the file, go to Properties in the Security section and put a checkmark on Modify (this is necessary to add changes);
3) Open the file, add the code to the <assemblyBinding> section and save the changes.
The code to add it is shown below.
<dependentAssembly>
<assemblyIdentity name=»Microsoft.VisualStudio.Shell.12.0″ publicKeyToken=»b03f5f7f11d50a3a» culture=»neutral»/>
<bindingRedirect oldVersion=»2.0.0.0-15.0.0.0″ newVersion=»15.0.0.0″/>
</dependentAssembly>
After analyzing many free alternative tool options (websites, Google plugins, command line applications, text editor add-ons), the choice fell on Poor Man’s T-SQL Formatter as an add-on for SSMS. The advantages and disadvantages of this add-on are described below.
Advantages:
- It’s free.
- Easy to install.
- Handles the tasks you set.
- Has a formatting settings window.
- Located in the development area directly.
- Open source code that allows you to make your own variations.
Disadvantages:
- Does not align text strings spread over multiple lines of code.
- Does not align comments /**/ spread over multiple lines of code.
