Free Instant SQL Formatter
Well apparently you can teach an old dog new tricks. I recently sent the following email to Enkitec’s internal techie email list:
Begin forwarded message:
Have you ever tried to decipher a really ugly, unformatted SQL statement that someone gave you or you pulled out of one the database views or an AWR report? Tim Fox turned me on to the SQL formatting capability of SQL Developer a few weeks ago (actually Brian Hill found it first and showed Tim). It is awesome! It’s a little unintuitive to use though. Here’s how I use it.
1. start up the SQL Developer (there are versions for Mac OS and Windows by the way)
2. Click File->New and choose the SQL File option
– this opens a Query Builder Pane (you don’t need to connect to a database)
3. Pick any random file to open
4. Paste your nasty 10 page long query into the Query Builder pane replacing the text from your random file
5. Right click on the text in the window (brings up a long menu)
6. Click the Format menu Item (it’s at the bottom of the menu in version 3.0)Viola – nicely formatted SQL text
The latest version of SQL Developer (3.0.x) is actually much better than the previous version by the way.
I haven’t closed SQL Developer since I found out about this capability!
SQL Developer is a free tool that can be downloaded from Oracle’s web site here:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Note version 3.1 is now available but I haven’t tried it yet.
There is also a shortcut to phase 5-6. Press Ctrl+F7. If you have several queries in the same file all queries are formatted. If you need to format only one query paint it before formatting.
Seeing the title on OraNa, I expected something like perltidy, a CLI script that would make my SQL pretty. SQL Developer is a free monster that takes 10 minutes to start and consumes 5 TB of memory. It is completely impractical for “pretty printing”, the same way that sledgehammer is not the right tool for fixing a picture on the wall.
Hi Mladen,
10 minutes to start SQLDeveloper – wow! You might want to consier upgrading. My 2 year old Mac Pro takes about 2 seconds. 😉 I’d agree if all the program did was format SQL but as you know it does a lot more. I use it for several things including Method-R plugin for grabbing and analyzing trace files. I just didn’t realize it had a very good SQL formatter built in to it.
Kerry
SQLDeveloper is a great tool, but one thing is bother me most about it: you can’t start another query to the same connection, if you already have an executing query..
@stee1rat
Yes you can: there’s a button labelled “Unshared SQL Worksheet (Ctrl + Shift + N)” on the toolbar in 2.1.1.64 and – I assume – all newer versions.
Cheers!
Flado
That’s what sqlplus windows are for! 😉
viola = Stringed Instrument http://en.wikipedia.org/wiki/Viola
Voila = french word for “here is it” http://en.wiktionary.org/wiki/voil%C3%A0
🙂
I also have used sql developer in the past including for formatting but I also wish it were less heavy… all java based Guis are sluggish in windows… I suspect that the reason you have not experienced this is because you use a mac…
Once you go Mac you can never go back!
I meant viola. I like the way it sounds better than wallah! (I’m kind of a phonetic guy) 🙂