Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.

Product Review: SQL Pretty Printer

SQL Pretty Printer for SQL Server Management Studio $39.95 single user $99.95 site license

having coding standards is a must for solid quality code. There are lots of articles on the subject like this one on simple talk.

Having standards is one thing, getting everyone on board is another, that’s where code formatters or beautifiers come in.

I’ve used code beautifiers for may other languages over the years and have written a couple to help enforce coding standards.

One of my pet peeves working with the default tools that ship with SQL Server is the lack of any kind of code formatter.

When you work with more than one developer you will get code that is ether hard to read or formatted to their specific taste.

That by its self isn’t a huge issue but when you are dealing with hundreds or thousands of stored procedures and other code bits written in T-SQL that you now have to dig into in can be a time sink. This is only compounded in an emergency where you are having to quickly look at a piece of code and figure out what exactly is going wrong.

To add insult to injury SQL Server will also help mangle the code for you and good. Heaven forbid you should script out a complicated view and watch about 400 lines of code squeezed into say 10.

There are some options out there to help with this, I had one criterion that had to be met though, it must integrate with SQL Server Management Studio. This one requirement stripped down the number of candidates very quickly.

If you need to format a smallish piece of code there are several online formatters, the authors of SQL Pretty Printer have one at http://www.dpriver.com/pp/sqlformat.htm.

So, with that requirement I found a couple at the time that met the requirements Red Gate SQL Refactor and you guessed it SQL Pretty Printer. SQL Refactor offers a lot more than code formatting and has one feature I really liked that expands wild cards into column listings, but they want $369.00 for it, per user. I also had a problem that after the trial expired I uninstalled and it left grayed out menu items in SSMS that now just annoy me, and I don’t know what to do other than a reinstall of SSMS to fix it.

After looking at SQL Refactor I loaded up SQL Pretty Printer.

It doesn’t offer some of SQL Refactor’s options but it does reformat the code, it also will convert your statements to work with C# and VB.Net. It will also grab sql statements out of those two languages and put them back as standard T-SQL.

After using it for a couple of weeks I really grew to like it, it was quick and easy CTRL-K-CTRL-H and *POOF* code was formatted, well most of the time we will get to that.

It has several customizations that control the formatting its pretty straight forward through the options dialog.

SQLPrettyPrint1

With the preview window its pretty quick to get the formatting the way you like it.

The things I like about SQL Pretty Printer:

Simple install and direct integration into SSMS/VS. This is just a huge time saver for me, there is a stand alone client that has more features but using SSMS all day not having to flip applications, cut and paste to format is a real win.

Fire and forget. Once you have it setup it just blends into the system a keystroke and everything is formatted, or just the text you select.

Inexpensive, Since the site licenses is low enough getting it installed on every developers machine isn’t a barrier giving everyone the ability to use the same code formatting standards.

The issues I have:

Formatting fails to render. Sometimes a block of code I have selected doesn’t format, instead it disappears. Yeah, it sounds bad but the CTRL-Z undo works so I haven’t lost anything. I will be submitting a bug report.

Unparseable code doesn’t format.If it isn’t a valid SQL statement it won’t attempt to format it. Not a huge deal, but I like to format as I go if possible.

Needs more formatting options. It’s hard to please everyone but you can start with me :). I would like to see more options for code style how things rap and split lines.

 

It’s simplicity and affordability make it a win in my book. If someone comes up with a better formatter at a cheaper price I’d love to see it.

 

Wes

Comments

Posted by Tim Mitchell on 5 September 2009

It's interesting how much effort goes into "prettification" of SQL code - I've heard similar discussions in the programming community.  I've got some of my personal standards (uppercase keywords and functions, indented subqueries, etc.) but sometimes I violate my own guidelines.

By the way, I use SQL Refactor (it came with my toolbelt).  I rarely use it on my own code, but I'll run it on someone else's if it's truly unreadable.  I've found it useful for expanding wildcards - if I have a really wide table I'll do a SELECT * and it'll drop in the column names for me.

Posted by Wesley Brown on 6 September 2009

Yeah I mentioned that same feature. We use SQL Backup but don't have the full tool belt.

Posted by Steve Jones on 6 September 2009

Can you reformat the same code differently on different workstations? I'm curious if the comma before/comma after preferences could be set for each user, on the same code.

In the past, I've made sure we had standards for coding. Not mine, necessarily, but standards to make sure that all code looks the same.

Leave a Comment

Please register or log in to leave a comment.