Years ago, I created an Add-In for Microsoft Access that was a toolbox of sorts, and I called it the DataFast Utility. It's still available for download, but never was anything more than a crude collection of scripts that I found personally useful. According to my records, it's been downloaded nearly 3000 times over the last five years though I've never gotten any feedback on it, good or bad.
One of the tools in my utility was a form with code that would accept an unformatted SQL query string and transform it into a more readable script. If you've ever looked at the SQL view for a complicated Access query you'll understand what I mean. My tool included the additional feature of formatting the text for use in a code module as embedded SQL assigned to a variable, as a Query suitable for the Access environment or with slight modifications to make it work in SQL Server Query Analyzer, with the delimiters modified to work in that environment.
You can still download my utility and play with it, but that's not what I'm here to talk about this week. I've found something better, and I'm actually kind of excited about it. The utility I'm talking about is actually a SQL Server Add-In that works with the new SQL Server Management Studio. It's called SQL Refactor and it's developed by Red-Gate Software.
If you've kept up with my product reviews, you'll know I'm fond of Red-Gate products, and SQL Refactor is no exception. Simply stated, it does the following:
- Reformat SQL Text
- Rename database objects
- Find unused variables
- Summarize your scripts
SQL Refactor is simple to use, as may be seen in the screen shot below. Once it is installed, you simply select options from the SQL Refactor menu while working in SQL Server Management Studio.
The Hows and Whys of Reformatting
The "How" of reformatting is simple. Just highlight the text you wish to reformat and select Lay Out SQL from the SQL Refactor menu. (I also frequently use the Uppercase Keywords command because I'm somewhat anal about things like that.) SQL Refactor comes with an Options page that allows you to set some, but not all, of your favorite preferences when it comes to how the scripts are formatted. Below are before and after screen shots of one particularly ugly query.
The "Why" of formatting is probably less obvious, but allow me to throw out some ideas.
I've known many Access developers who've ventured into the realm of SQL Server. Years ago, that meant giving up the Query-By-Example designer and getting used to writing your own SQL. Not so anymore. Now SQL Server Management Studio includes an Access-like designer for queries (originally code-named DaVinci or Delgatto or Lorenzo ... I forget) where you can create queries (views) by adding tables and dragging fields. It also generates the same unformatted junk that you get in Microsoft Access when you use the QBE designer.
While an Access developer could spend his whole life and never leave the QBE for the sexy and exciting world of raw SQL, that's not true for those who work in SQL Server Management Studio. The first screen shot below is a view that someone created using the designer. I had to modify the view, but I hate the designer, so I faced the prospect of adding white space and reformatting the text by hand. Then came SQL Refactor and with a single click of the mouse, I was on my way, reading and editing the script with ease.
Rename, Analyze and Summarize
The next feature of SQL Refactor is its Object Rename function. I had some screen shots prepared, but opted not to include them. Like all the features of SQL Refactor, the rename process is simple and intuitive. There's really nothing to it. Just follow the wizard and pray that you typed the new object name correctly. Sure, there are times you need to rename objects, but this decision shouldn't be made lightly ... or often. However, if you have to do it, SQL Refactor can help by providing a script of all the changes required. Still, be careful!
The Summarize feature will help you work with more complex objects, like Stored Procs and Triggers. For example, there was a rather complex stored proc that had been created by a brilliant DBA on our staff. I had to review the proc to see how pending changes to the database might affect it, but didn't know where to start. I applied the summarize script function to the proc and the results may be seen in the screen shot below. Notice how the dialog box summarizes each distinct SQL script in the procedure and the Tree-View nature of the summary allows you to drill down into subscripts. This is a great tool!
The last feature I'll discuss is the analysis function, Find Unused Variables and Parameters. Now we're back to anal again. It probably doesn't matter if you created a variable and didn't use it, though it presumably takes up memory unnecessarily. Apply this to a script and you'll see little green squiggly lines under variables that are declared and set but not used. I showed the results to the brilliant DBA who created the proc, and she was shocked to see she'd forgotten to clean up her variables. I guess it could happen to anyone.
Another fine product from Red-Gate
I love Red-Gate tools, especially SQL Compare, but now SQL Refactor is poised to take first place. This is the kind of tool I use all day long and having it on the menu of SQL Server Management Studio makes it the most convenient utility I've reviewed in some time. The $295 price tag is not trivial, but manageable and well in line with the price of this genre of Add-In. There are aspects of this utility that, if used often, will allow you to recover that cost through saved development time, but by far the most important reason I will always use SQL Refactor is to avoid the frustration that inevitably comes from working with complex SQL.