Dirty SQL? Clean It Up With Poor Man’s T-SQL Formatter

SQL Poor Man's T-SQL FormatterEveryone likes code that’s easy to read. NOT everyone writes code that’s easy to read. That’s why, for my SQL work, I like to keep the Poor Man’s T-SQL Formatter handy.

Poor Man’s T-SQL Formatter is an open source tool created by Tao Klerks that, as it’s name implies, formats SQL code. Formatting code with this tool corrects indentation, changes all of your SQL keywords to uppercase, expands expressions, and has options to do all sorts of other nifty things. The result – Code that’s easier to read, follow, and understand.

Having this SQL formatter is helpful in several ways. I frequently look at code developed by others. Everyone has different coding styles, and sometimes code is nice to begin with. Other times you get code from a contractor that was done as quickly as possible to meet a deadline and is a jumbled mess. But nice or messy, running it through the formatter will give me a nice readable format that I am used to seeing. I don’t always write code perfectly formatted myself, so I make sure I run my SQL through the formatter before I check anything in to source control. I find the formatter especially helpful to use on SQL that’s saved directly in an rdl or dtsx, where stuff gets auto stripped out of the code and ends up looking all bunched up together. Copy it into an SSMS window and reformat and it becomes much easier to read.

The developer was even nice enough to add a special option to Poor Man’s T-SQL Formatter for me a while back. In one of my old team’s projects we had a chunk of security code we built into one or two hundred stored procedures. We had it layed out nice, neat, and easily understandable in about 8 lines, but not exactly formatted according to any scheme. Running it through Poor Man’s would expand it to about 30-40 lines. It still looked good, but because we used it so often I wanted to keep it a small block of SQL that took up little space. So I posted an issue up on Tao’s github for the project. What I requested was an option to implement a way to “comment” a section of code from the formatter’s perspective so the formatter would leave it alone. My idea was to have special tags you could put in SQL comments that would indicate the start and end of an area that the formatter would be able to understand.

The developer surprised we with a quick implementation, and had two new options for me to test. He created [noformat][/noformat] tags to retain the original formatting, and [minify][/minify] tags to “scrunch up” the code. The new options work like a charm and helped me to make my project’s code look that much nicer.

I use the SSMS add-in, but there are also online, console, and other incarnations of the SQL formatter available. There’s even a library available to include in your own GPL/AGPL projects if you’re inclined to do so. So if you haven’t tried a SQL formatter before, go and grab the Poor Man’s T-SQL Formatter and see how it works for you. And as always, feel free to comment or ask any questions you might have.

Leave a Reply

Your email address will not be published. Required fields are marked *