Clean T-SQL FormatterA VS Code formatter extension for T-SQL. Installation Launch VS Code Quick Open ( Ctrl+P), paste the following command, and press enter.Copied to clipboard |
|
Clean T-SQL Formatter| Setting | Type | Default | Description |
|---|---|---|---|
tsqlFormatter.breakOnKeywords |
boolean |
true |
Break into a new line for keywords like WHERE, JOIN, AND, OR. |
tsqlFormatter.keywordCase |
string |
"preserve" |
Keyword casing mode ("upper", "lower", or "preserve"). |
tsqlFormatter.identifierCase |
string |
"preserve" |
Identifier casing mode for database/schema/table/column names ("upper", "lower", or "preserve"). |
tsqlFormatter.useBrackets |
boolean |
false |
Wrap table/column identifiers in square brackets (e.g., [TableName]). Variables (@var, @@sysvar) and temp tables (#temp, ##global) are never bracketed. |
tsqlFormatter.linesBetweenQueries |
number |
2 |
Number of empty lines between top-level SQL statements. |
tsqlFormatter.useMaxLineLength |
boolean |
true |
Enable line length wrapping. |
tsqlFormatter.maxLineLength |
number |
100 |
Maximum line length before wrapping long expressions onto consistently indented continuation lines. |
Example .vscode/settings.json:
{
"tsqlFormatter.keywordCase": "upper",
"tsqlFormatter.identifierCase": "preserve",
"tsqlFormatter.useBrackets": false,
"tsqlFormatter.linesBetweenQueries": 2
}
Examples
Basic Query
Before:
select id, name, email from dbo.users where is_active=1 and created_at >= '2025-01-01' order by name
After (with keywordCase: "upper"):
SELECT
id,
name,
email
FROM dbo.users
WHERE is_active = 1
AND created_at >= '2025-01-01'
ORDER BY name;
Multi-Row INSERT ... VALUES & Table Value Constructors
Before:
insert into dbo.Users (id, username, email) values (1, 'alice', 'alice@example.com'), (2, 'bob', 'bob@example.com'), (3, 'charlie', 'charlie@example.com');
After:
INSERT INTO dbo.Users (id, username, email)
VALUES
(1, 'alice', 'alice@example.com'),
(2, 'bob', 'bob@example.com'),
(3, 'charlie', 'charlie@example.com');
Derived (VALUES ...) table value constructors in FROM / APPLY clauses are formatted with the same row alignment:
SELECT
*
FROM (
VALUES
(1, 'A'),
(2, 'B')
) AS t(id, code);
Subqueries, CTEs & Table-Valued Functions
WITH ActiveUsers AS (
SELECT
user_id,
email
FROM dbo.users
WHERE status = 'ACTIVE'
)
SELECT
u.user_id,
u.email,
s.value AS tag
FROM ActiveUsers AS u
CROSS APPLY STRING_SPLIT(u.tags, ',') AS s
WHERE u.user_id IN (
SELECT
user_id
FROM dbo.orders
WHERE total_amount > 100
);
MERGE Statement
MERGE INTO dbo.TargetTable AS target
USING dbo.SourceTable AS source
ON target.ID = source.ID
WHEN MATCHED AND source.Val <> target.Val THEN
UPDATE SET target.Val = source.Val, target.Updated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Val)
VALUES (source.ID, source.Val)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action, inserted.*, deleted.*;
Comment Formatting
Input:
SELECT 1;
-- first comment
-- second comment
SELECT id,
-- comment before column
name FROM users;
/* block comment */
SELECT 2;
Output:
SELECT
1;
-- first comment
-- second comment
SELECT
id,
-- comment before column
name
FROM users;
/* block comment */
SELECT
2;
Modern DDL & Indexes
CREATE TABLE dbo.Orders (
OrderID INT IDENTITY(1, 1) NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME2(7) DEFAULT SYSUTCDATETIME() NOT NULL,
TotalAmount DECIMAL(18, 2) NOT NULL,
TaxAmount AS (TotalAmount * 0.1) PERSISTED,
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID ASC),
CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES dbo.Customers (CustomerID) ON DELETE CASCADE
);
CREATE UNIQUE NONCLUSTERED INDEX IX_Orders_CustomerDate ON dbo.Orders (CustomerID ASC, OrderDate DESC)
INCLUDE (TotalAmount)
WHERE TotalAmount > 0
WITH (DATA_COMPRESSION = PAGE, FILLFACTOR = 80, ONLINE = ON);
ALTER TABLE dbo.Orders ALTER COLUMN TotalAmount DECIMAL(19, 4) NOT NULL;
Transactions & Stored Procedures
CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
@CustomerId INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
SAVE TRAN SavePoint1;
DROP TABLE IF EXISTS #TempOrders;
SELECT
order_id,
order_date
FROM dbo.orders
WHERE customer_id = @CustomerId;
COMMIT WORK;
END;
Troubleshooting
- Undo Changes: Use VS Code's Undo (
Ctrl+Z/Cmd+Z) if formatting is not desired for a specific file. - Selection Formatting: Select full, complete SQL statements when running Format Selection for best results.
- Variables & Temp Tables:
useBracketsignores variables (@var,@@sysvar) and temp tables (#temp,##global) per T-SQL syntax standard. - Output Channel: If an error occurs, check Output -> TSQL Formatter in VS Code for details.
- Issues & Contributions: Report issues or request features at GitHub Issues.
Development & Contributing
Project Structure
src/extension.ts— VS Code extension activation and formatting provider registration.src/provider.ts—DocumentFormattingEditProviderandDocumentRangeFormattingEditProviderimplementation, configuration loading, and output logging.src/formatter.ts— Synchronous (formatTsql) and asynchronous (formatTsqlAsync) SQL formatting engine (SqlFormatter).src/tokenizer.ts— Lexer (tokenize,tokenizeAsync) and greedy multi-word keyword combiner (mergeMultiWordKeywords,mergeMultiWordKeywordsAsync).src/keywords.ts— T-SQL reserved keywords, multi-word keyword trie definitions, built-in functions, and parameterized data types.
Build & Test
To build and run tests locally:
# Install dependencies
npm install
# Compile TypeScript
npm run compile
# Run full test suite
npm test
# Watch mode during development
npm run watch