Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

...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("SELECT CODE_FACL, SEQ_FACL, FILLER_TOF ");
    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("    , CODE_FACL_I_T, INTKEY_FACL, IND_CDM_SENTENCING ");
    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(creator);
    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
share|improve this answer
    
Is your product (the formatter) available online or to download? –  jullins Oct 14 '11 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. –  Gilbert Le Blanc Oct 14 '11 at 16:47
    
I would appreciate it, I just want to see the the formatting part. –  jullins Oct 14 '11 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. –  Gilbert Le Blanc Oct 14 '11 at 17:24
    
what about the code? Can you put it somewhere? –  jullins Oct 21 '11 at 19:34
show 1 more comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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