# CodeLake TSqlFormatter

A desktop application that formats T-SQL scripts into clean, consistently styled output. Also supports headless CLI mode for automation and CI/CD pipelines.

## Getting Started

1. Launch the application.
2. Paste your T-SQL into the **Unformatted SQL** pane.
3. Select a formatting style from the **Style** dropdown.
4. The **Formatted SQL** pane updates automatically.
5. Formatted SQL is copied to the clipboard automatically for your convenience.

## CLI Mode

Run the formatter headless from the command line without opening the GUI:

```
TSqlFormatter.exe <styleName> <inputFile> <outputFile>
```

| Argument | Description |
|----------|-------------|
| `styleName` | Name of the formatting style (case-insensitive): `Classic`, `Classic Minimalist`, `Comfortable`, `Comfortable Minimalist`, `Microsoft SQL Server Parser` |
| `inputFile` | Path to the file containing unformatted SQL |
| `outputFile` | Path where the formatted result will be written (UTF-8, no BOM) |

The CLI uses the same formatting pipeline as the GUI. On parse errors or runtime failures, the output file will contain a comment block with error details followed by the original SQL.

The exit code reports the outcome, so a build or CI step can tell a finished run from a failed one:

| Code | Meaning |
|------|---------|
| `0` | The SQL was formatted and written |
| `1` | The requested style does not exist (the output lists the available styles) |
| `2` | The input could not be read, or `outputFile` is the same path as `inputFile` |
| `3` | The output could not be written |
| `4` | An unexpected failure |

`outputFile` may not be `inputFile`: if that run were the one to fail there would be no copy of the SQL left. Nothing is written in that case - the exit code is the only report, since writing one would overwrite the input being protected.

## Styles

| Style | Description |
|-------|-------------|
| **Classic** | Uppercase keywords, aligned clause bodies (`SELECT`, `FROM`, `WHERE` in a column) |
| **Classic Minimalist** | Same layout as Classic with lowercase keywords |
| **Comfortable** | Uppercase keywords, each clause keyword on its own line with indented body |
| **Comfortable Minimalist** | Same layout as Comfortable with lowercase keywords |
| **Microsoft SQL Server Parser** | Uses Microsoft's ScriptDom generator directly - guaranteed lossless |

### Classic

```sql
SELECT   EmployeeId,
         Name,
         Salary
FROM     Employees
WHERE    DepartmentId = 5
         AND IsActive = 1
ORDER BY Salary DESC;
```

### Classic Minimalist

```sql
select   EmployeeId,
         Name,
         Salary
from     Employees
where    DepartmentId = 5
         and IsActive = 1
order by Salary desc;
```

### Comfortable

```sql
SELECT
    EmployeeId,
    Name,
    Salary
FROM
    Employees
WHERE
    DepartmentId = 5
    AND IsActive = 1
ORDER BY
    Salary DESC;
```

### Comfortable Minimalist

```sql
select
    EmployeeId,
    Name,
    Salary
from
    Employees
where
    DepartmentId = 5
    and IsActive = 1
order by
    Salary desc;
```

### Microsoft SQL Server Parser

Uses Microsoft's `Sql180ScriptGenerator` with default options. Use this when the other styles show a warning about unformatted parts, or for complex SQL constructs not yet supported by the custom formatters.

## Supported SQL

SELECT, INSERT, UPDATE, DELETE, MERGE, CTEs, CASE expressions, window functions, JOINs (all types including CROSS/OUTER APPLY), subqueries, UNION/INTERSECT/EXCEPT, CREATE/ALTER TABLE/VIEW/PROCEDURE/FUNCTION/TRIGGER, IF/WHILE/BEGIN...END, TRY...CATCH, cursors, transactions, PIVOT/UNPIVOT, FOR XML/JSON, OFFSET/FETCH, BULK INSERT, temp tables, table types, sequences, GRANT/DENY/REVOKE, indexes, DROP IF EXISTS, SET options, EXEC/sp_executesql, PRINT/RETURN, WAITFOR, GOTO, table hints, STRING_AGG, comment preservation, and GO batch separators.

## Error Handling

Your SQL is never lost or silently altered. Whatever goes wrong, the output is either correctly
formatted SQL or your original SQL with an explanatory comment.

- **Syntax errors** - Displayed with line and column numbers, followed by your original SQL unchanged.
- **Unformatted parts** - If the selected style can't handle a construct, a warning is shown at the top and the output falls back to the Microsoft SQL Server Parser style, so you still get valid formatted SQL.
- **Runtime errors** - An unexpected failure in the formatter degrades the same way, with a diagnostic comment. Details are written to the log folder.
- **Unsupported constructs** - Some newer T-SQL (e.g. Fabric Data Warehouse `AS CLONE OF`, `WITH (CLUSTER BY)`, `AS EXTERNAL FUNCTION`) cannot yet be parsed by ScriptDom itself. These are reported as a parse error with your SQL returned unchanged, rather than being mangled.

## Notes

- Settings are persisted between sessions.
- Comments (both `--` and `/* */`) are preserved in their original positions.
- GO batch separators are preserved - including trailing GO and comments after the final GO.
- **`--#SQL_BATCH_SEPARATOR_DO_NOT_REMOVE#--` works as a batch separator.** If your deployment tooling strips
  `GO`, you can write that comment where a `GO` belongs and the script still formats - even though such a
  script does not parse on its own. The marker comes back in the output, never accompanied by a `GO`, and any
  `GO` you wrote yourself is left exactly where it is.

  Blank lines around the marker are **normalised to one above and one below**, whether you wrote none, one or
  six - it is a separator, so its spacing is laid out for you - and it is written flush left, like `GO`.
  Formatting the output again changes nothing.
- The `;WITH` prefix on CTEs ensures they work after other statements without a preceding semicolon. It is
  used only where a statement may begin - a batch, a `BEGIN…END` block, or a procedure, function or trigger
  body. A CTE inside a view, an inline table-valued function's `RETURN`, a `DECLARE CURSOR FOR`, or the single
  statement of an `IF`, `ELSE` or `WHILE` is written as plain `WITH`, because a leading semicolon is not valid
  in those positions.
- The Minimalist styles produce identical structure to their parent - only keyword casing differs.