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.
Click on Formatted SQL tab and see the results as follows.
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.
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.
Open the generated file formatted_myquery.sql with SSMS and we can see the following formatted T-SQL code.
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.
Then select Format T-SQL Code from the SSMS Tools menu.
Here is the code after the formatting.
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.
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
