Poor Man's T-SQL Formatter

Modified on 2013-10-28 03:16 by TaoK — Categorized as: C#, dotNet 2, IIS, SQL

Try it now on PoorSQL.com, the free
online TSQL formatting service
!

Overview

This is a free and open-source SQL (T-SQL) formatter:
This formatter does implement a high-level SQL tokenizer and parser, but the granularity of the parser is not very high. It does not distinguish between different types of DML, it does not parse full expression trees, etc - there's a lot it doesn't do, it just does the bare minimum to support the target formatting.

The library is largely complete, but undocumented. For the list of known issues, please see the issue tracker on github. As there is only as much parsing detail as necessary to support the desired formatting, the parse tree format is still not finalized; many formatting enhancements require new elements in the parse tree.

The code is hosted on GitHub at https://github.com/TaoK/PoorMansTSqlFormatter; I'm always very grateful for any feedback on the functionality, the code or any other aspect of the project!

To keep an eye on new releases or other related news, Follow @PoorSQL

Tweets by @PoorSQL

I also send out occasional announcement emails to people who've contacted me - email me at if you'd like to get these announcements (this might eventually become a real mailing list).



Download

Ready-to-run:




Compiled Library to use in other GPL/AGPL projects or internal non-distributed projects:
Source & Web Service:

Latest Changes

2013-10-23, version 1.5.3 (SSMS / Visual Studio Add-In only)


2013-10-22, version 1.5.2 (SSMS / Visual Studio Add-In only)


2013-10-20, version 1.5.1


2012-11-11, version 1.4.4 (SSMS / Visual Studio Addin only)


2012-09-09, version 1.4.3 (library + poorsql.com + windows Forms only)


2012-09-09, version 1.4.2 (library only)


2012-09-02, version 1.4.1


2012-03-12, version 1.3.1


2012-01-29, version 1.2.1:
2012-01-21, version 1.1.1:
Full Change Log:
Known / Open Issues:

License / Redistribution

The library and accompanying programs are released under the Affero GPL. This means that you are allowed to make any changes you want, there are restrictions/requirements attached only if you choose to redistribute the original or modified code: If you choose to redistribute/expose the functionality, then you must make the source available to the recipients/users (even of a web service). For private usage, you can make any changes you want and never need to tell anyone; if you distribute the app/functionality or a service that relies on it, then you must also distribute the source. For more details, please see the AGPL article on wikipedia or at the source, the FSF (free software foundation).

At least for the moment (as I'm the only one who has contributed to the project so far), the project could be dual-licensed if you really, really wanted to use it in a commercial program. If so please contact me, I'm sure we could work something out.

Tips & Tricks


Feedback & Known Issues

Please email me at , or simply create issues in the GitHub issue tracker.

Instant Demo

The demo has been moved to poorsql.com - check it out and contact me with any issues!

Contributing

So far I haven't received any contributions from anyone else, but contributing is easy with GitHub! Just fork the project, make your changes, and create a Pull Request for me to take a look and I will (if I agree with the changes of course) happily incorporate them!

https://github.com/TaoK/PoorMansTSqlFormatter

For translations, I was originally using "Amanuens", a translation platform developed by Dario Solera (the original developer/maintainer of Screwturn Wiki) and a couple of his friends, that sadly had to shut down a few months ago. At this point I haven't looked fotr a replacement yet, the level of interest generated by the translation work was, at best, very low (and that makes sense for a developer-focused tool: a very large proportion of international developers speak enough english to get by).

Or if you just want to show your support, you can report your use of the project on Ohloh:

Background

But Why??

You may wonder why create yet another SQL formatting library... I've asked myself that a few times as I've been working on this. The main reason is that when I have searched for this in the past, I've never found a tool that did exactly what I wanted it to. Another answer is that it is fun, and I think I can do a pretty good job, and I've learned quite a lot about T-SQL in the process. Finally, I hadn't found any open-source library that I thought I could help improve, rather than starting my own (when I started this project, that is).

There does appear to be at least one other active/in-progress open-source project out there (sqlformat, by Ben Laan), but I don't believe that the approach taken there (as far as I can tell from looking at the code and playing around) could scale to the type of full-script universal T-SQL formatting I want to be able to (and now can, as far as I can tell) perform.

The main challenge, I believe, is that I want to do full re-formatting of *any* T-SQL code, supporting all keywords and syntax constructs. To do this with a full T-SQL parsing model would be difficult, or maybe even unrealistic, to achieve in a one-man (or few-people) part-time open-source project. By scaling it back to a partial parsing model where only formatting-relevant constructs are singled out, the scope of the project is significantly reduced, hopefully to an achievable and maintainable level.

The library is currently "V1 Complete", with no major bugs or omissions that I am aware of - I will probably be scaling back my attention to this project unless/until someone contacts me with suggestions or bugs. I have a list of "Issues" in the github issue tracker, but none are significant enough that I expect to work on them in the coming weeks.

Other Products and Projects

To get a better idea of the value of this project (for myself and potentially others), I started putting together a quick comparison table for this project vs all the other products and projects I come across. Obviously I try to avoid undue bias, and equally obviously I will still be biased towards this project, as I am human :).

Here is the list of all T-SQL formatting tools I've encountered, with a comparison table below:
Please Note: This table is woefully out of date! I haven't reviewed this list for over a year (as of December 2012), and I have received corrections or additional information from at least one person (the author of the TidyCode formatter) which I haven't had a chance to review/update.





Poor Man's T-SQL Formatter SqlFormat .Net 3.5 Formatter Library Instant SQL Formatter OracleFAQs SQL and PL/SQL Formatter SQL Pretty Printer General SQL Parser T-SQL Tidy Red Gate Simple-Talk Code Prettifier Red Gate SQL Prompt SQLInForm Free Online Version SQLInForm Desktop or Server Version manoli.net c# code format Navicat Lite RazorSQL DevArt SQL Complete T-SQL Beautifier SQLEnlight SoftTree SQL Assistant Quest Toad for SQL Server Apex SQL Refactor Tidycode T-Sql Formatter SQLIse / SQLPSX SqlFormat Online SQL Formatting service SilverlightSQLConvert
ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
Open-Source ?
Free (not trialware) ? ? ?
Works Offline ?
Online Access (website) ?
Command-line exe ½ ½ ? ? ? ? ?
SSMS Plugin ?
Cross-platform ½ ? - - ? - - - ? ? ½ - ½ ?
Other SQL Dialects ? ? ½ ? ? ?
Embeddable/Library/API - - ? ? ?
Exposes Parse Tree ½ ? ? ? ? ?
Procedural SQL (multi-statement, flow control, etc) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
Graceful degradation (unhandled sql) ? ? ? ? ? ? ? ? ? ? ?
Multi-batch & DDL handling ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
NHibernate Appender ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
Edge Cases: Numerics & Currency ? ? ? ? ? ? ? ? ? ?
Edge Cases: Nested Comments ? ? ? ? ? ? ? ? ? ?
"Aligned" formatting ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
Coloring (HTML) ? - - ? ? ? ? ? ? ? ? ? ?
Keyword Standardization ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
Code Wrapping (eg VBScript) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
Recent Activity (last 6 months) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?


Please note:

Your Formatting preferences? Who are you?

The only reason my personal preferences are important here, is that I'm the one writing this :).

What I can do, however, is explain the rationale behind my preferences, in the hope that I might sway someone's opinion:

What if I want my SQL formatted differently?

I am completely open to providing additional formatting options, obviously within the time I have available to devote to this project. As it's open source and hosted on github, it's also trivial to fork the project, download to your local machine and get cracking on any changes you'd like to see!

If you don't want to, or can't make the changes yourself and you can't get a hold of me or I don't have time to work on the changes, there is always the host of commercial products identified above!