TSQL Code Formatting with Microsoft ScriptDOM library

The Microsoft ScriptDOM library, which comes with SSMS as well, enables the parsing, validation, and formatting of any T-SQL code.

It's strange that Microsoft doesn't make the formatting feature available in SSMS by default, but we're addressing that issue here.

The standard SqlScriptGenerator generates code with several aspects that I find inconvenient, and I've made additional formatting adjustments to address these:

1. The second table in the JOIN statement will be on the same line.
2. The ON clause of the JOIN will be indented by 4 spaces.
3. CROSS APPLY/JOIN statements will start on a new line and be indented by 4 spaces.
4. For CASE WHEN .. THEN .. ELSE .. END statements:
   • WHEN will start on a new line and be indented by 4 spaces.
   • THEN will start on a new line and be indented by 8 spaces. The inner statement will be uniformly indented to the left.
   • ELSE will start on a new line and be indented by 4 spaces.
   • END will start on a new line.

Do you have more ideas what can be done better? Let me know.

These changes are made through the query tree object, ensuring compatibility with any code. You need to opt-in for these additional code transformations in the settings. If the additional transformations result in an error, the output will revert to the default scripting.

Example

The source query text:

The default SqlScriptGenerator output:

The output with additional transformations: