How to format a SQL query into a more human-readable format?

Question

I'm looking for a program that can take in an SQL string, (my environment is MySQL, but I could look at similar tools for other RDBMSs) parse it and render it again in a format that is more human-readable. I've seen some online ones, but I'm hoping for one that I can integrate into some internal logging tools.

For example, taking:

SELECT * FROM table JOIN other_table ON table.id = other_table.id WHERE column = 'value' AND column = 'value'

and converting it into:

SELECT *
FROM table
   JOIN other_table
      ON table.id = other_table.id
WHERE column = 'value'
   AND column = 'value'

My environment is specifically PHP, but I wouldn't mind spending time porting code.

Answer 1

You could have a peek at phpmyadmin and see how they are formatting SQL. They always pretty-print every query that was executed.

In libraries/common.inc.php there is a function called PMA_formatSql, this might be a good starting point.

Edit: It does not neccessarily display the SQL as HTML formatted string. Depending on the configuration (implemented as global :-( ) it does output the SQL as text.

Answer 2

Google has a javascript code prettifier which has a plugin for SQL:

http://code.google.com/p/google-code-prettify/

Answer 3

I've been using SQLYog as a querying tool, and it comes with a "prettyfier" built in that works with most of the queries I try. However, it's not free.

If this is to be used in your logging, you could probably make it look for and insert newline characters before specific keywords like FROM, WHERE, AND.