Simon Thorneycroft and Jonathan Hodgson

Can't read Sql because it's too messy

Continuing the Sql theme... sure we have all been there before where you're looking at someone else's (or your own) sql and it's a bit of mess.

I often use this little tool which can reformat sql code from this:

SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

to:

SELECT
 Orders.ShippedDate,
 Orders.OrderID,
 [Order Subtotals].Subtotal,
 DATENAME(yy, ShippedDate) AS Year
FROM
 Orders
INNER JOIN
 [Order Subtotals]
ON
 Orders.OrderID = "Order Subtotals".OrderID
WHERE
 Orders.ShippedDate Between @Beginning_Date AND @Ending_Date

After you install it, ignore the evaluation splashscreen this first time and click close, then it sits in the taskbar. You'll probably want to change the options, normally I switch oneline off and set keywords to uppercase on. Then copy the sql to the clipboard and press the hotkey Ctrl+Shift+J and then paste the newly formatted sql back. It's not perfect, but it is handy for quickly cleaning up sql.

You can download niceSql from http://home.broadpark.no/~ihalvor/

Seems I'm not the only one looking for better tool support in Sql Server as Dan Fernandez has some great ideas.

Also Apex Sql Edit has some good features like Intellisense, better editor, unit testing, etc.

Whilst we're on the subject, in Sql Server 2005 nearly all the dialog boxes have been improved so they are no longer modal and you can make all your changes and then view those changes as a script for later use; useful for learning Sql syntax for backup and restore commands.