Standardize SQL Server T-SQL Code Formatting


By:   |   Updated: 2017-05-30   |   Comments (6)   |   Related: More > T-SQL


Problem

Within the last 15+ years of working as an MSSQL DBA and Developer for small, medium and large corporations the simple task of creating a standardized T-SQL coding style amongst developers has always been a challenge. In this tip I will show an effective and free tool that can help a development team adopt a common coding style.

Solution

The Poor Man's T-SQL Formatter offers a brilliant and good solution to create a consistent standardized T-SQL code formatting.

Let's see it in action with a series of examples:

Web Based SQL Formatter

Go to Poor Man's T-SQL Formatter and copy and paste the following hard to read code.

Select tbla.Name, tbla.LstName, tblB.PhoneNum, tblC.Address
from tblUsrName tbla join tblUsrPhone tblB on tblA.usrID = tblB.usrID
left join tblUsrAddr tblC on tblC.usrID =tblausrID join (select workplace, workadr, workrole from tblOfice) z join tblB on
z.ID=tblB.OfficeID where tblc.Stane in ('GA','AL','NY','CA')

Here is the code I pasted into the tool.

Poor Man's T-SQL Formatter

Click on Formatted SQL tab and see the results as follows.

Poor Man's T-SQL Formatter

In my opinion, the results produced are much easier to read.

SQL Formatter SSMS Add-In

The only issue with the above option is that it is a web based solution and is not integrated with SSMS.

To fully integrate the formatting component within SSMS we will have to download and install the add-in SqlFormatterSSMSAddIn.Setup.1.5.3.msi for SSMS as shown below.

Poor Man's T-SQL Formatter

The installation steps are very easy, just double click on the .msi file and follow the setup instructions. The interesting part is that the tool will install two components, a command line formatter and an add-on for SSMS.

Format SQL Code in File

Let's format T-SQL code that is saved in a file.

Save the T-SQL code from above in a .sql file, such as myquery.sql and execute the following command:

SqlFormatter myquery.sql /o:formatted_myquery.sql

The above command will take the input from the file myquery.sql and produce a formatted .sql file named formatted_myquery.sql.

SqlFormatter.exe

Open the generated file formatted_myquery.sql with SSMS and we can see the following formatted T-SQL code.

Poor Man's T-SQL Formatter

Format SQL Code in SSMS Query Window

Let's now see how it integrates with SSMS. To use the SSMS add-in component we will have to close and then open SSMS after the install of the add-in.

Then open a SSMS query window, highlight the code that needs to be formatted.

SSMS Integration

Then select Format T-SQL Code from the SSMS Tools menu.

SSMS Integration

Here is the code after the formatting.

SSMS Integration

SQL Formatter SSMS Add-in Settings

The beauty of the add-in is that it has a formatting option dialog box that lets the user customize the formatting style.

SSMS Integration

Conclusion

In my opinion, the formatting tool can be used as foundation to set a standard for T-SQL code writing amongst developers.

Troubleshooting

If you are not able to see the formatter integrated with your version of SSMS please make sure to copy PoorMansTSqlFormatterSSMSAddIn.AddIn to the following locations:

SQL 2012
C:\Users\All Users\Microsoft\SQL Server Management Studio\11.0\Addins

SQL 2014
C:\Users\All Users\Microsoft\SQL Server Management Studio\12.0\Addins

Restart SSMS when the file copy completes.

References

Understand how SQL Server works to write better T-SQL queries.

Creating custom T-SQL conditions and policies for SQL Server Policy Based Management.

Next Steps
  • Try to install Poor Man's T-SQL Formatter and create a standardized code style for all of the developers
  • Evaluate the formatting options available in Poor Man's T-SQL Formatter


Last Updated: 2017-05-30


get scripts

next tip button



About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips



Comments For This Article




Wednesday, January 15, 2020 - 9:37:07 AM - Jeff Moden Back To Top (83783)

While I love the capabilities of PoorSQL, I've found that it is overwhelmed by larger more complex code.  Have a "Plan B" for those instances.


Tuesday, August 14, 2018 - 4:41:35 AM - Andreas Neumann Back To Top (77169)

Here is one more great service for beautifying SQL code - Online SQL Formatter http://sql-format.com


Monday, May 14, 2018 - 2:13:44 PM - Yun Back To Top (75940)

Does this module have any nuget package or equivalent that support programatically calls?

 


Monday, June 05, 2017 - 6:57:23 AM - Matteo Lorini Back To Top (56763)

The answar to the question: " Does it work on SSMS 2016?" Is, I do not know. I have only tested it on sql 2012 & 2014. Will let you know as soon as I can test it on 2016.

 

Thanks


Thursday, June 01, 2017 - 3:33:25 AM - Jean MBADI Back To Top (56398)

 Definitely a great add-in. It differentiates very well indentation and formating are awesome. 

Thank you Mateo,

Regards

 


Tuesday, May 30, 2017 - 1:51:11 PM - Dan Back To Top (56284)

 Does it work on SSMS 2016?



download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms