Page History: Poor Man's T-SQL Formatter
Compare Page Revisions
Page Revision: 2017-11-22 07:00
Overview
This is a free and open-source SQL (T-SQL) formatter:
- Handles complete multi-batch scripts, including object definition scripts such as stored procedures, triggers, etc.
- Provides formatting options to cater to different common formatting styles/standards
- Optionally outputs "colorized" html code rather than just the formatted SQL
- Also provides "minifier" option to strip out comments and whitespace, to obfuscate rather than pretty-print your code
- Available ready-to-use in a variety of forms
- SSMS (SQL Server Management Studio) and Visual Studio Addin/Extension that allows you to format the current file or selected text with a single hotkey - supports any version of SSMS or SSMS Express, and any Full (not Express) version of Visual Studio.
- Notepad++ plugin, for quick single-key formatting in your favorite general-purpose text editor.
- Command-line utility that lets you format any number of files in bulk, or format from some other arbitrary program - for windows (.Net) or any environment (node/npm)
- Winforms app for easy offline formatting (also lets you look at the token stream and parse tree)
- WinMerge plugin, for automatically formatting SQL files before comparison, allowing WinMerge to display content changes only, ignoring formatting differences.
- JS library that exposes the same functionality in any browser or other Javascript-based context, used in the demo/online formatting site http://poorsql.com
- Also available as a .Net 2.0/3.5 library, downloadable here or through NuGet
- Written in C# using a pluggable design that should allow for other SQL dialects to be supported in future
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,
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:
- Notepad++ plugin: It's recommended to use the Notepad++ plugin manager if available (Notepad++ is an amazing free GPL-licensed text editor, available at notepad-plus-plus.org; it has a very rich plugin community available through the "Plugin Manager" plugin. This is no longer installed by default, but adding it is very easy, and some replacement will presumably be made available shortly).
- If you need to install the plugin manually, you can get it from here: SqlFormatterNppPlugin.1.6.10.zip (15368 downloads for the latest version, about 400000 for previous versions) and unzip the entire contents of the ZIP file (1 DLL + 1 folder containing other DLLs and stuff) into your Notepad++ install folder's "plugins" subfolder, eg "C:\Program Files\Notepad++\plugins". If you get an error about the plugin not supporting unicode, then the wrong DLL is in the plugins folder, simply delete the incorrect DLLs and start again (please contact me for help if you have any issues with this).
- 64-bit version : SqlFormatterNppPlugin.x64.1.6.10.zip (5298 downloads for the latest version, about 350 for previous versions)
- Command-line bulk formatting utility
Compiled Library to use in other GPL/AGPL projects or internal non-distributed projects:
JS Library to use in other GPL/AGPL projects or internal non-distributed projects:
Source:
Latest Changes
2017-11-21, version 1.6.10
- Released Atom Editor plugin
- Released VS Code extension
- Fixed rare negative exponent problem, GitHub issue #142
- Fixed "name" keyword detection issue, as per GitHub pull request #141
- Fixed recently-introduced issue with "noformat" regions, GitHub Issue #182
- Fixed longstanding formatting consistency issues with noformat and minimize, GitHub issues #183, #184, #185
- Split VS and SSMS VSIX packages to avoid errors & confusion with marketplace VSIX install on SSMS
2017-11-05, version 1.6.6
- Fixed cosmetic "Argument Error" under specific circumstances in VS and SSMS, GitHub Issue #168
- Fixed undo stack issue in SSMS / VS, GitHub Issue #120
- Fixed "format selection always adds extra newlines" issue (untracked)
- Fixed SSMS/VS/NPP plugin translation files / fix outrageous layout glitches in translated forms, GitHub Issue #41
- Fixed tab order in demo app and plugin settings form, GitHub Issue #177
- Published new JS library in NPM: https://www.npmjs.com/package/poor-mans-t-sql-formatter
- Published new JS-based command-line formatting utility for unixey environmentss on NPM: https://www.npmjs.com/package/poor-mans-t-sql-formatter-cli
2017-10-17, version 1.6.1 bis bis bis (SSMS / Visual Studio Add-In, and SSMS Extension)
- New support for SSMS 2014-17 with the Extension Installer for SSMS
- Long-term supportability of older VS and SSMS versions with new Add-In installer
- Notepad++ 64-bit package made available for download
2017-10-15, version 1.6.1 bis bis (Visual Studio Extension only)
2017-08-13, version 1.6.1 bis (Poorsql.com only)
- minor bugfixes to JS library
2017-07-28, version 1.6.1 (Poorsql.com only)
- JS-based formatting - faster, more reliable, and works offline (AppCache)
2013-10-23, version 1.5.3 (SSMS / Visual Studio Add-In only)
- Github Issue #109: (Fix to Github Issue #86): Visual Studio 2012 support was never tested before release, and turned out to be broken.
2013-10-22, version 1.5.2 (SSMS / Visual Studio Add-In only)
- Github Issue #107: [Bugfix] Unexplained install failures on SOME machines [thanks Jerrad Elmore for the bug report]
2013-10-20, version 1.5.1
- Github Issue #85: [Enhancement] Merged code cleanup changes by Timothy Klenke
- Github Issue #87: [Enhancement] Added support for SQLite bit-shift and c-style equality operators [thanks Tom Holden for the request]
- Github Issue #86: [Enhancement] Added support for Visual Studio 2012 [thanks Jarred Cleem for the request]
- Github Issue #81: [Enhancement] Added support for C-Style comments ("//") and colon-prefixed parameter/host-variable names for other SQL dialects [thanks Paul Toms for the request]
- Github Issue #90: [Enhancement] Merged more code cleanup / options simplification changes by Timothy Klenke
- Github Issue #47: [Enhancement] Separated option for expansion of IN lists [thanks to a number of people for the request: smartkiwi, defrek, Marvin Eads and Bill Ruehle]
- Github Issue #95: [Enhancement] Corrected display of chained ELSE IF statements to be more cross-language-standard [thanks to steelwill for the request]
- Github Issue #100: [Bugfix] Corrected crash when the string for format ends with Greater Than or Less Than signs
- Github Issue #97: [Bugfix] Parsing error when single uppercase N is followed by a non-word character [thanks to derekfrye for the bug report]
- Github Issue #91: [Bugfix] Incorrect formatting of "*=" operator [thanks to ralfkret for the bug report]
- Github Issue #51: [Enhancement] Added support for the OUT synonym of OUTPUT (for arguments), and corresponding keyword standardization support
- Github Issue #49: [Enhancement] Added options to control line spacing between clauses and statements [thanks to Farzad Jalali, Sheldon Hull and Benjamin Solomon for the request]
- Github Issue #96: [Enhancement] Enhanced SSMS, VS and NPP plugins to keep cursor at the approximate previous location when reformatting whole document [thanks to Paul for the request]
2012-11-11, version 1.4.4 (SSMS / Visual Studio Addin only)
- Github Issue #79: [BugFix] Fix SSMS Add-In to work in unexpected languages [thanks Paulo Stradioti for the bug report]
- Github Issue #16: [New Feature] Add Visual Studio support to the Add-In
2012-09-09, version 1.4.3 (library + poorsql.com + windows Forms only)
- Github Issue #75: [Enhancement] Add parse error highlighting in HTML output [thanks Jeff Clark for the suggestion]
2012-09-09, version 1.4.2 (library only)
- Github Issue #70: [Enhancement] Eliminate "Client Framework"-incompatible dependency on System.Web [thanks to Brad Wood and Richard King for feedback on this]
2012-09-02, version 1.4.1
- Github Issue #54: [New Feature] Support for DB2/SQLLite string concatenation operator [thanks to numerous people for suggesting this, sorry about the delay]
- Github Issue #48: [Bugfix] Correction to SQL 2012 add-in install folder
- Github Issue #52: [Bugfix] Fix to space-indent saving in formatting plugin options (SSMS, Notepad++) [thanks to numerous people for reporting this, sorry about the delay]
- [Bugfix] Fix examples in commandline help to correspond to existing options [thanks John Landmesser for the bug report]
- Github Issue #74: [Bugfix] Correct parsing & formatting of subqueries & function calls in variable initializers
- Github Issue #73: [Bugfix] Correct parsing & formatting of OPTION clauses
- Github Issue #55: [Bugfix] Correct parsing & formatting of OUTPUT specifiers on proc arguments defined without parens
2012-03-12, version 1.3.1
- Github Issue 23: [New Feature] Support for SSMS 2012 / Denali
- Github Issue 34: [New Feature] Obfuscating formatter / minifier in Winforms app and Web Service
- Github Issue 36: [New Feature] Support for "[noformat][/noformat]" and "[minify][/minify]" block-formatting instructions in standard formatter [thanks dquille for the request]
- Github Issue 37: [Bugfix] SSMS Hotkey binding failed in non-english versions of SSMS [thanks Philipp Schornberg for the bug report]
- Github Issue 38: [Bugfix] Comment-positioning bugs, one of which could result in invalid output SQL! [thanks andywuest for the bug report]
- Github Issue 39: [Bugfix] Formatting Library not usable in .Net 3.5 and later projects, because of Linqbridge conflict on Linq namespace [thanks Sean Bornstein for the bug report]
- Github Issue 40: [Bugfix] Error parsing WITH options containing "ON" in parentheses [thanks Jean-Luc Mellet for the bug report]
2012-01-29, version 1.2.1:
- Github Issue 32: [Feature Request] Enhanced Cmdline Utility to support pipeline input (and output) [thanks William Lin for the request]
- but default behaviour unchanged, still expect to act on files directly unless piped input provided
- expected encoding is UTF-8... in powershell, for example, run "$OutputEncoding = [System.Text.Encoding]::UTF8" first.
- Enhanced Cmdline Utility "no files found" message to include extensions detail
- Github Issue 26: Testing enhancements:
- Changed testing framework to Nunit, included dependencies for building and testing in any environment
- Simplified testing code, with test sources
- Added tests for different formatting options / flags, greater coverage
- GitHub Issue 31: Completed Notepad++ Plugin, with formatting options - available trough Notepad++ plugin manager.
2012-01-21, version 1.1.1:
- Translated programs, ssms plugin + website into French and Spanish
- thanks to Threeplicate for Amanuens, a free (for open-source projects) localization tool that makes managing translations much easier!
- and thanks to my wife for proofreading... technical translations are always a mess, but at least I got a second pair of eyes on it!
- Initial Beta of Notepad++ plugin [thanks UFO and Robert Giesecke for the .Net managed plugin template!]
- See http://sourceforge.net/projects/notepad-plus/forums/forum/482781/topic/4911359 to get the plugin beta
- Corrected "Request Validation" issue on poorsql.com, caused by over-zealous web service validation in ASP.Net 4.0
- Github Issue 22: [Bugfix] Indenting of "AS" clause on 2nd or later CTE in a query
- GitHub Issue 21: [Feature Request] Added optional ignore of errors in CmdLine Formatter [thanks Jörg Burdorf for the request]
- GitHub Issue 18: [Bugfix] Corrected positioning of comments (after linebreak) at end of statements [thanks gvarol for the bug report]
- GitHub Issue 19: [Feature Request] Added optional indenting of join ON sections [thanks Pushpendra Rishi for the request]
- GitHub Issue 24: [Bugfix] HTMLEncoding missing in Web Service (eg for poorsql.com) and Winforms demo app [thanks Gokhan Varol for the bug report]
- GitHub Issue 25: [Feature Request] Settings persistence and optional display simplification in Winforms demo app [thanks Gokhan Varol for the request]
- GitHub Issue 33: [Bugfix] Culture-specific uppercasing bug (Turkish) [thanks Recep Guzel]
- GitHub Issue 35: [Enhancement] WITH clause breaking [thanks Lane Duncan for the suggestion]
- Softcoded parsing error message in library (for translation + optional removal of warning)
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
- If you don't want a certain block of code to be reformatted, then just put a comment containing [noformat] before and [/noformat] after, like this:
SELECT 1,
2,
3
-- [noformat] don't want any formatting here, I like it with spaces before AND after the commas here.
SELECT 4 , 5 , 6 , 7 , 8 , 9
--[/noformat]
SELECT 1,
2,
3
- If you want to minimize the space taken by a chunk of SQL, and don't care about legibility, you can minify it with a comment containing [minify] before and [/minify] after, like this:
SELECT 1,
2,
3
--[minify]
SELECT 4AS[Four],5AS Five,6,7,8,9
--[/minify]
SELECT 1,
2,
3
- Loren Halvorson pointed out you can use the command-line formatter to pre-format SQL files in Beyond Compare, with a command like this:
SqlFormatter –o "%t" "%s"
- Paul Kohler shared a registry change he uses to format files from the Windows Explorer context menu (save as something.reg and run it):
Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\*\shell\Reformat SQL]
[HKEY_CLASSES_ROOT\*\shell\Reformat SQL\command]
@="C:\\Tools\\SqlFormatter.1.2.1\\SqlFormatter.exe \"%1\" /is:\" \" /tc+"
- If you're the author of a SQL-related tool and you want to make use of the formatting library, but you don't want to release your project under the AGPL license, then you can always use the command-line formatter as an optional external tool. If you then redistribute the command-line tool you need to publish/provide the source of the formatter tool, but you don't need to relicense your project to match. Even simpler, you can suggest that users download the formatter separately to enable formatting functionality.
Feedback & Known Issues
For general feedback please email me at
.
To browse existing / known issues or add new ones to be tracked, please use 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 many contributions, 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/PoorMansTSqlFormatterI need to formulate a "Contributor Agreement" to be able to accept any significant contributions, but that should be quick enough to do as the need arises, please let me know if you're interested.
Or if you just want to show your support, you can report your use of the project on Ohloh:
Background
Other Products and Projects
There is a comparison of all the SQL Formatting products that I know of, in wiki (editable) form, in the project wiki at
https://github.com/TaoK/PoorMansTSqlFormatter/wiki/SQL-Formatter-comparisons.
(Previously there was a comparison table here, but it was years out of date and not editable by anyone but me. That was silly.)
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:
- I find that too much whitespace in the body/joins distracts from the structure of the query, especially if you end up having to scroll to find parts of the query. This is why I'm not fond of indenting the "ON" portion of a join clause
- I find that logical expressions are easiest to read (and write) when each portion is on its own line. This applies regardless of whether it's in a join clause's "ON" conditions, or the "WHERE" clause
- I find it very important to be able to write SQL (in a variety of editors) in the target format, without needing something to clean up after you. This is the main reason I don't like "Aligned" SQL formatting. I understand it can be more legible, but it's too much of a pain to maintain (esp. in embedded/ad-hoc SQL queries). It also breaks when you use tabs (in different editors with different tab stops), and I like tabs! They're easy-to-navigate whitespace!
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. Existing open requests are tracked on the
GitHub issue tracker, with prefix "Option: ". As the project is 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!