Poor Man's T-SQL Formatter

Modified on 2019-08-04 11:51 by TaoK Categorized as C#, dotNet 2, IIS, SQL
<div style="float: right; padding: 15px; border: 1px solid darkgray; background: white; font-weight: bold; font-size: 120%; line-height: 140% ">Try it now on <a href="http://poorsql.com/">PoorSQL.com, the free <br /> online TSQL formatting service</a>!</div>
==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

<div style="float: right; padding-left: 5px; ">{toc}</div>
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 [https://github.com/TaoK/PoorMansTSqlFormatter/issues|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, <a href="https://twitter.com/PoorSQL" class="twitter-follow-button" data-show-count="false" data-dnt="true">Follow @PoorSQL</a>
<nowiki><script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src="//platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs");</script></nowiki>

<a class="twitter-timeline" href="https://twitter.com/PoorSQL" data-widget-id="392595733435531266">Tweets by @PoorSQL</a>
<script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+"://platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs");</script>

I also send out occasional announcement emails to people who've contacted me - email me at <script language="javascript">
eval(unescape('function%20z%28%29%20%7Bvar%20a%3D%27zib.s%27%3Bvar%20b%3D%2'+
'7oat%27%3Bvar%20c%3D%27krelk%27%3Bvar%20f%3D%27@%27%3Bvar%20'+
'g%3D%27%27%3Bvar%20h%3D%27%27%3Bvar%20n%3D%27%27%3Bvar%20i%3'+
'D%27%3Ca%20href%3D%22mailto%3A%27%3Bvar%20l%3D%27%22%20class'+
'%3D%22%27%3Bvar%20k%3D%27%22%20style%3D%22%27%3Bvar%20m%3D%2'+
'7%22%3E%27%3Bvar%20j%3D%27%3C/a%3E%27%3Bvar%20d%3Di+e%28b%29'+
'+f+e%28c%29+e%28a%29+l+g+k+h+m%3Bd+%3D%28n%3D%3D%27%27%29%3F'+
'e%28b%29+f+e%28c%29+e%28a%29%3An%3Bd+%3Dj%3Bdocument.write%2'+
'8d%29%3Bfunction%20e%28s%29%7Bvar%20t%3Ds.split%28%27%27%29%'+
'3Bt.reverse%28%29%3Bs%3Dt.join%28%27%27%29%3Breturn%20s%7D%7'+
'Dz%28%29%3B'));</script> if you'd like to get these announcements (this might eventually become a real mailing list).

<script type="text/javascript" src="http://www.ohloh.net/p/596313/widgets/project_thin_badge.js"></script>

==Download==

Ready-to-run:
* '''Extension for CURRENT VERSIONS of Visual Studio (2015-2019)''' (VSIX Package): Download from the Visual Studio Marketplace: [https://marketplace.visualstudio.com/items?itemName=TaoKlerks.PoorMansT-SqlFormatterSSMSVSExtension]

* '''Extension for CURRENT VERSIONS of SSMS (2014-18)''' (setup program): '''[{UP(PoorMansTSqlFormatter)}PoorMansTSqlFormatterSSMSPackage.Setup.1.6.16.msi|PoorMansTSqlFormatterSSMSPackage.Setup.1.6.16.msi]''' <countDownloads pattern="(#COUNT# downloads for this version, about 20,000 for previous versions)"><file name="..." /><attachment name="PoorMansTSqlFormatterSSMSPackage.Setup.1.6.16.msi" page="PoorMansTSqlFormatter" /></countDownloads>. 

* '''Visual Studio Code extension''': On the Visual Studio Marketplace: [https://marketplace.visualstudio.com/items?itemName=TaoKlerks.poor-mans-t-sql-formatter-vscode|https://marketplace.visualstudio.com/items?itemName=TaoKlerks.poor-mans-t-sql-formatter-vscode]

* '''Atom Editor plugin''': In the Atom Package Manager: [https://atom.io/packages/poor-mans-t-sql-formatter|https://atom.io/packages/poor-mans-t-sql-formatter]

* '''Notepad++ plugin''': It's recommended to use the '''Notepad++ plugin manager''' if available (Notepad++ is an amazing free GPL-licensed text editor, available at [http://notepad-plus-plus.org/|notepad-plus-plus.org]; it has a very rich plugin community available. Historically this was accessed through the "Plugin Manager" plugin, but this is no longer installed by default. The new Plugin -> Admin feature doesn't yet support Poor Man's T-SQL Formatter, but should soon). 
** If you need to install the plugin manually, you can get it from here: [{UP(PoorMansTSqlFormatter)}SqlFormatterNppPlugin.1.6.13.zip|SqlFormatterNppPlugin.1.6.13.zip] <countDownloads pattern="(#COUNT# downloads for the latest version, about 410000 for previous versions)"><file name="..." /><attachment name="SqlFormatterNppPlugin.1.6.13.zip" page="PoorMansTSqlFormatter" /></countDownloads>.
*** For Notepad++ 7.5.9 and earlier, 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". 
*** For Notepad++ 7.6 to 7.6.2, '''upgrade'''. These versions had inconsistent plugin-loading folders (flip-flopping all over the place).
*** For Notepad++ 7.6.3 and later, if properly installed via installer, create a new folder called "PoorMansTSqlFormatterNppPlugin" in your Notepad++ install folder's "plugins" subfolder, eg "C:\Program Files\Notepad++\plugins\PoorMansTSqlFormatterNppPlugin", and unzip the entire contents of the ZIP file (1 DLL + 1 folder containing other DLLs and stuff) into this new subfolder. It's almost the same as before 7.6, except now the contents of the ZIP file ned to be in a correctly-named '''subfolder''', not directly in the plugins folder.
**** If you've got a "portable" Notepad++ setup, then the old approach (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) should still be the right thing.
*** 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 : [{UP(PoorMansTSqlFormatter)}SqlFormatterNppPlugin.x64.1.6.13.zip|SqlFormatterNppPlugin.x64.1.6.13.zip] <countDownloads pattern="(#COUNT# downloads for the latest version, about 4350 for previous versions)"><file name="..." /><attachment name="SqlFormatterNppPlugin.x64.1.6.13.zip" page="PoorMansTSqlFormatter" /></countDownloads>

* Extension for '''Visual Studio 2013''' (VSIX Package): Download from the Visual Studio Marketplace: [https://marketplace.visualstudio.com/items?itemName=TaoKlerks.PoorMansT-SqlFormatterSSMSVSExtension2013]

* Add-In for '''OLD VERSIONS of SSMS''' (2005-2012) and Visual Studio (2005-2012) (setup program): '''[{UP(PoorMansTSqlFormatter)}PoorMansTSqlFormatterSSMSAddIn.Setup.1.6.10.msi|PoorMansTSqlFormatterSSMSAddIn.Setup.1.6.10.msi]''' <countDownloads pattern="(#COUNT# downloads for this version, about 130000 for previous versions)"><file name="..." /><attachment name="PoorMansTSqlFormatterSSMSAddIn.Setup.1.6.10.msi" page="PoorMansTSqlFormatter" /></countDownloads>. 

* '''WinForms App''' (simple exe): '''[{UP(PoorMansTSqlFormatter)}SqlFormatterWinforms.1.6.13.zip|SqlFormatterWinforms.1.6.13.zip]''' <countDownloads pattern="(#COUNT# downloads this version, about 32000 for previous versions)"><file name="..." /><attachment name="SqlFormatterWinforms.1.6.13.zip" page="PoorMansTSqlFormatter" /></countDownloads>

* '''Command-line bulk formatting utility''' 
** Windows / .Net exe file: '''[{UP(PoorMansTSqlFormatter)}SqlFormatter.1.6.10.zip|SqlFormatter.1.6.10.zip]''' <countDownloads pattern="(#COUNT# downloads for this version, about 14000 for previous versions)"><file name="..." /><attachment name="SqlFormatter.1.6.10.zip" page="PoorMansTSqlFormatter" /></countDownloads>
** Linux (and others) node-based commandline tool: Installable via npm - [https://www.npmjs.com/package/poor-mans-t-sql-formatter-cli|https://www.npmjs.com/package/poor-mans-t-sql-formatter-cli]

* '''WinMerge Plugin''' for SQL Script comparison (setup program): '''[{UP(PoorMansTSqlFormatter)}PoorMansTSqlFormatterWinMergePlugin.Setup.1.5.1.msi|PoorMansTSqlFormatterWinMergePlugin.Setup.1.5.1.msi]''' <countDownloads pattern="(#COUNT# downloads for this version, about 2000 for previous versions)"><file name="..." /><attachment name="PoorMansTSqlFormatterWinMergePlugin.Setup.1.5.1.msi" page="PoorMansTSqlFormatter" /></countDownloads> (WinMerge is a free GPL-licensed file comparison tool, available at [http://winmerge.org/|winmerge.org])

Compiled Library to use in other GPL/AGPL projects or internal non-distributed projects:
* [{UP(PoorMansTSqlFormatter)}SqlFormatterLibrary.1.5.1.zip|SqlFormatterLibrary.1.6.13.zip] - .Net 2.0 version <countDownloads pattern="(#COUNT# downloads for this version, about 1200 for previous versions)"><file name="..." /><attachment name="SqlFormatterLibrary.1.6.13.zip" page="PoorMansTSqlFormatter" /></countDownloads> 
* [{UP(PoorMansTSqlFormatter)}SqlFormatterLibrary_35.1.6.13.zip|SqlFormatterLibrary35.1.6.13.zip] - .Net 3.5 and later version <countDownloads pattern="(#COUNT# downloads for this version, about 1700 for previous versions)"><file name="..." /><attachment name="SqlFormatterLibrary_35.1.6.13.zip" page="PoorMansTSqlFormatter" /></countDownloads> 
* (there's also a [http://nuget.org/packages/PoorMansTSQLFormatter/|NuGet package], kindly set up and maintained by Adam Pawsey - but now quite outdated)

JS Library to use in other GPL/AGPL projects or internal non-distributed projects:
* NPM library: [https://www.npmjs.com/package/poor-mans-t-sql-formatter|https://www.npmjs.com/package/poor-mans-t-sql-formatter]

Source:
* Please download from github at [https://github.com/TaoK/PoorMansTSqlFormatter]. If this is impractical for whatever reason please contact me, and I'll make the source available by some other mechanism.

==Latest Changes==

=== [1.6.16] - 2019-08-04 ===

===== Changed =====

* Added VS 2015 back into latest/current extension, aborted "2015" extension and created a legacy 2013 extension.
* Fixed dependency issues in SSMS packages.

=== [1.6.15] - 2019-08-01 ===

===== Changed =====

* Split VS Extension into a (new) legacy 2013-2015 package, and current 2017-2019 package, to add async loading support in modern VS

=== [1.6.14] - 2019-07-30 ===

===== Changed =====

* Fixed bad SSMS and VS Packages

=== [1.6.13] - 2019-07-27 ===

===== Added =====

* SSMS 18 and VS 2019 Support

=== [1.6.11] - 2017-11-24 ===

==== Added ====

* Appveyor build configuration, as per proposal in Pull Request #178 from chcg

==== Changed ====

* Fixed newly split SSMS Extension Installer to set correct registry key at install time. 
* Restructured changelog to follow [Keep a Changelog](http://keepachangelog.com/)

Full Change Log:
* See the [https://github.com/TaoK/PoorMansTSqlFormatter/blob/master/CHANGELOG.md|changelog on github]

Known / Open Issues:
* See the [https://github.com/TaoK/PoorMansTSqlFormatter/issues|issue tracker on github]

==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 [http://en.wikipedia.org/wiki/Affero_General_Public_License|AGPL article on wikipedia] or at the source, the [http://www.gnu.org/licenses/agpl.html|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 &amp; Tricks==

* If you don't want a certain block of code to be reformatted, then just put a comment containing <nowiki>[noformat]</nowiki> before and <nowiki>[/noformat]</nowiki> after, like this: 
{{{{<nowiki>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
</nowiki>}}}}
* 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 <nowiki>[minify]</nowiki> before and <nowiki>[/minify]</nowiki> after, like this:
{{{{<nowiki>SELECT 1,
    2,
    3
--[minify] 
SELECT 4AS[Four],5AS Five,6,7,8,9
--[/minify] 
SELECT 1,
    2,
    3
</nowiki>}}}}
* Loren Halvorson pointed out you can use the command-line formatter to pre-format SQL files in Beyond Compare, with a command like this: {{{{<nowiki>SqlFormatter –o "%t" "%s"
</nowiki>}}}}
* Paul Kohler shared a registry change he uses to format files from the Windows Explorer context menu (save as something.reg and run it): {{{{<nowiki>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+"
</nowiki>}}}}
* 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 
<!-- conveniently generated at http://purplefoot.com/jsemail.html -->
<script language="javascript">
eval(unescape('function%20z%28%29%20%7Bvar%20a%3D%27zib.s%27%3Bvar%20b%3D%2'+
'7oat%27%3Bvar%20c%3D%27krelk%27%3Bvar%20f%3D%27@%27%3Bvar%20'+
'g%3D%27%27%3Bvar%20h%3D%27%27%3Bvar%20n%3D%27%27%3Bvar%20i%3'+
'D%27%3Ca%20href%3D%22mailto%3A%27%3Bvar%20l%3D%27%22%20class'+
'%3D%22%27%3Bvar%20k%3D%27%22%20style%3D%22%27%3Bvar%20m%3D%2'+
'7%22%3E%27%3Bvar%20j%3D%27%3C/a%3E%27%3Bvar%20d%3Di+e%28b%29'+
'+f+e%28c%29+e%28a%29+l+g+k+h+m%3Bd+%3D%28n%3D%3D%27%27%29%3F'+
'e%28b%29+f+e%28c%29+e%28a%29%3An%3Bd+%3Dj%3Bdocument.write%2'+
'8d%29%3Bfunction%20e%28s%29%7Bvar%20t%3Ds.split%28%27%27%29%'+
'3Bt.reverse%28%29%3Bs%3Dt.join%28%27%27%29%3Breturn%20s%7D%7'+
'Dz%28%29%3B'));</script>.

To browse existing / known issues or add new ones to be tracked, please use the [https://github.com/TaoK/PoorMansTSqlFormatter/issues|GitHub issue tracker].

==Instant Demo==

'''The demo has been moved to [http://poorsql.com/|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/PoorMansTSqlFormatter]

I 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: <script type="text/javascript" src="http://www.ohloh.net/p/596313/widgets/project_users_logo.js"></script>

==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|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 [https://github.com/TaoK/PoorMansTSqlFormatter/issues|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!
Meta Keywords:
Meta Description: consistency fixes
Change Comment:
Architect Shack

Navigation

Poor Man's T-SQL Formatter
