Using UltraEdit for SQL editing

My former manager asked why I use UltraEdit for all of my SQL Server coding, and after pouring out the answer, I figured I should post it here as well for posterity.

There’s so many advantages you just take for granted after a while. I had to open it and do some editing to remember what all they were.

SQL Syntax Highlighting

It has color-coded syntax highlighting for just about any language imaginable via add-on word files. (Download the T-SQL word file off UltraEdit.com and append it to the end of the installed wordfile.txt to enable it.)

It automatically highlights the matching parenthesis. When your cursor is on a beginning parenthesis, UltraEdit highlights the matching end one, regardless of which line it’s on. That’s a troubleshooting lifesaver.

It can automatically use spaces instead of tabs, and you can set the number of spaces to use for each press of the tab button. Tabs work differently across different editors (Query Analyzer, Enterprise Mgr, UltraEdit, Notepad, etc), but spaces work exactly the same. Makes the code much easier to read.

It automatically indents and unindents based on the words you type. (This is defined in the wordfile.txt for each language.) When you start a “begin” statement and hit enter, for example, it automatically tabs in.

SQL Templates and Indenting

It has support for templates and code snippets built in. I have to confess that I never use those, though.

It can quickly comment out code. Highlight the lines you want to comment out, and click Edit, Comment Add.

UltraEdit theoretically has the ability to re-indent code like HTMLtidy cleans up HTML, but UltraEdit’s method is woefully inadequate. Instead, check out this online SQL formatter. It’s like HTMLtidy, but for SQL. Call it SQLtidy – or at least, they should have. Copy/paste a small stored proc in there and it fixes the indenting, cases, and puts one item per line. Great stuff. I ran across this when trying to clean up some horrendous sql at the new gig. Just standardize on a set of options, and then have everybody clean up their sp’s in here before they check them into CVS.

Plus, that tool even facilitates inline SQL – in the “Output” dropdown at the top, pick the programming tool you’re using and it’ll build the string for you, nice and clean.

Automatic Code Cleanup From The Command Line

There’s a command line utility for download and you can even somewhat integrate it into UltraEdit. Install the command line util, and then run it once from inside UltraEdit via Advanced, DOS Command. UE has a variable for the filename and can set the working directory, so once you’ve ran the command line utility, you can run it again for other sql files pretty easily. Just make sure you save your file before you run the command, so you don’t lose your changes.

I never got ambitious enough to use this company-wide because people have wildly different syntax styles, but this really makes it easy to standardize and clean things up. Saved me a ton of time at the new place – the prior “DBA” used Enterprise Manager’s graphical query builder to build all of his queries, and the [syntax].[and].[MIXEDCASE].[made].[bABYjESUS].[Cry].

File Comparison for Stored Proc Versions

UltraEdit also has a decent file compare utility. Use CVS to locally save the prior version of a stored proc, then open both the current and the prior in UE. Click File, Compare Files, and it’ll show both files side by side with the differences highlighted. Makes it easy to test new versions of sp’s and views because you can see exactly what changed. There’s also an extra-cost higher-powered file compare called UltraCompare, but it’s not worth the money if you’re just comparing stored procs.

It has some other obscure features, like search and replace with regular expressions syntax, but just as far as day-to-day commonly used stuff goes, UltraEdit blows the doors off Query Analyzer.