I need a tool (for in house usage) that will format SQL code (SQL Server/MySQL).
There are various 3rd party tools and online web sites that do it but no exactly how I need it.

So I want to write my own tool that will fit my needs.

First question is there any standard or a convention for how the SQL code should be formatted? (the tools that I tried format it differently)

The second question, how should I approach this task? Should at first convert the sql query into some data structure like a Tree?

2 Answers 2


...is there any standard or a convention for how the SQL code should be formatted?

Standard, no. You can put an entire SQL statement on one line as far as an SQL parser is concerned.

Convention, sure there are lots. It depends whether you're trying to maximize changeability or minimize space. I've written SQL formatters for both cases.

I just used particular character combinations to tell me where to break the SQL statement.

Here's one example from a Java DB2 SQL formatter that I wrote. Another Java program generated the Java code. The SQL came directly from the SYSIBM tables.

protected void prepareIndex00Select(String codeFacl)
        throws SQLException {
    StringBuffer sb = new StringBuffer();
    sb.append("    , CODE_TOF, NAME_FACL, NAME_LENGTH ");
    sb.append("    , CODE_FMB, ID_NCIC_ORI, NBR_PRINTER_PREFIX ");
    sb.append("    , ID_PERSONNEL_OFC, COMPLEX_CODE ");
    sb.append("    , PHS_CODE, DESIG_FACL_GRP, IND_DESIG_AUTH ");
    sb.append("    , MAL_FEM_IND, DEL_AFTER, IND_INMATES ");
    sb.append("    , VALUE_SO_CPU_STD, VALUE_SO_CPU_DAY ");
    sb.append("    , CODE_CAT, VALUE_DCN, XIDBKEY ");
    sb.append("    , FACL_FK_REGN ");
    sb.append("  FROM ");
    sb.append(".FACL ");
    sb.append("  WHERE CODE_FACL = ? ");
    if (additionalSQL != null) sb.append(additionalSQL);

    psIndex00 = connection.prepareStatement(sb.toString());
    psIndex00.setString(1, codeFacl);

}   // End prepareIndex00Select method
  • Is your product (the formatter) available online or to download?
    – jullins
    Oct 14, 2011 at 14:11
  • @jullins: No. I wrote it just to prove that I could write a Java application that writes Java classes, as well as building the SQL from the database column and index tables (SYSIBM). Sadly, no one I work with has found it useful. I suppose I could put the code somewhere if you want. Oct 14, 2011 at 16:47
  • I would appreciate it, I just want to see the the formatting part.
    – jullins
    Oct 14, 2011 at 17:22
  • @jullins: I'm at work now, so I can't access public repositories. I'll put the code up somewhere this weekend, and let you know how to access it. Oct 14, 2011 at 17:24
  • what about the code? Can you put it somewhere?
    – jullins
    Oct 21, 2011 at 19:34

A little late, only just stumbled across this, sorry.

Poor Man's T-SQL Formatter is an open-source T-SQL formatter (library, ssms plugin, command-line file formatter, etc) - the implementation is reasonably modular and it shouldn't be very hard to implement a MySQL tokenizer and formatter to match the T-SQL ones (I haven't done so primarily because I have no experience of or use for MySQL right now, so it's not a good use of my time).

The library is implemented in C# (2.0) with an AGPL license - that means you can't redistribute it commercially or expose as a public service without publishing any modifications, but for in-house user it should present no issues, whether it's customized or not.

As @Gilbert Le Blank already answered, there is definitely no standard on SQL formatting - even the commercial formatters out there, with the different options they provide, don't converge on the same defaults or even necessarily support the same output formats.

With respect to writing your own tool from scratch, I would advise against it if you need to handle a variety of cases: at least for T-SQL, handling SQL multi-statement batches with CTE WITH clauses, MERGE statements, sub-queries and derived tables, etc turns out to be pretty hard :)

In case it's any help: http://www.architectshack.com/PoorMansTSqlFormatter.ashx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.