T-SQL Toolbox

A toolbox for working with SQL Server in VS Code, built on top of the Microsoft mssql extension:

• ⚡ Auto-replacements: type ?top, press Tab, and it expands into a full query with the cursor already in place.
• 🔎 Go to Implementation: right-click a table, view, procedure, function or trigger and open its source code, ready to edit.
• 🧾 Data scripts: generate INSERT, UPDATE and DELETE statements from table rows, from a SELECT query or from results copied from the grid.
• ⚠️ Missing WHERE warning: DELETE and UPDATE statements without a WHERE clause are highlighted in the editor.
• 🗂️ Management panel: see, search and edit every auto-replacement in one place, and manage your connections.
• 🚦 Environment indicator: a coloured status bar item telling you whether you are on PROD, TEST or anywhere else.

Documentación en español: docs/README.es.md.

Auto-replacements

Auto-replacements are short tokens that expand into SQL text. The extension starts empty: nothing is created without asking. Run Add starter auto-replacements if you want a generic set to begin with (?top, ?count, ?tran, ?try, ?exists, ?findobj, ?findcol, ?findtext, ?sessions, ?cols), or create your own.

• Tab expands the token right before the cursor. If there is no token, Tab works as usual (indentation, snippet navigation and suggestion acceptance are not affected).
• Typing ? opens the list of your auto-replacements with a preview of the SQL. Keep typing to filter.
• Search and insert (Command Palette → Search and insert auto-replacement) finds one by token, description or content.
• If two auto-replacements share the same token, you choose which one to insert.

Creating auto-replacements

From a selection (fastest): select some SQL, right-click → Create auto-replacement from selection, and type the token. If the text contains /*cursor*/, the cursor will be placed there when it expands.

SELECT *
FROM dbo.Customers
WHERE LastName LIKE '%/*cursor*/%'

By editing the file: run T-SQL Toolbox: Edit auto-replacements file. It uses the same format as VS Code snippets:

{
  "?top": {
    "prefix": "?top",
    "body": ["SELECT TOP (${1:100}) *", "FROM $0"],
    "description": "Top N rows"
  }
}

The file is reloaded automatically when you save it.

Importing and exporting

You can import any .code-snippets or .json snippets file — one a colleague sent you, one you exported from another machine, or one you produced with any other tool — and export your auto-replacements as native VS Code snippets to share them with people who do not use this extension.

Go to Implementation

Right-click the name of an object → Go to Implementation (SQL Server). A new editor opens with:

• Procedures, functions, views and triggers: the original source code as CREATE OR ALTER (configurable as ALTER or CREATE).
• Tables: the full CREATE TABLE script, generated by the scripting service of the mssql extension (the same engine SSMS uses).

It understands:

• Bracketed and qualified names: Customers, [dbo].[Customers], OtherDb.dbo.Customers.
• Aliases in the query: in FROM dbo.Customers c, clicking c (or the c in c.Name) opens Customers.
• Synonyms, which are resolved to the object they point to.
• Names that exist in several schemas: you choose which one to open.

Find objects and their uses

• Find an object in the database… searches every table, view, procedure, function and trigger by name and opens its implementation. You do not need the name in the editor first.
• Find where this object is used lists the procedures, views and triggers that reference the object under the cursor, combining the real dependencies (sys.sql_expression_dependencies) with a text search of the module definitions, which also catches dynamic SQL. Pick one and its code opens.

Script as

Right-click a table, view, procedure or function → Script as (SELECT, INSERT, UPDATE, EXECUTE…), the way SSMS does it:

Every column is written out, with SSMS-style placeholders (<Name, nvarchar(100),>). UPDATE and DELETE use the primary key in the WHERE clause and warn when the table has none. EXECUTE writes the parameter list and declares the OUTPUT ones.

Data scripts (INSERT / UPDATE / DELETE)

Right-click → Generate data scripts (INSERT/UPDATE/DELETE) and choose which statements you want. There are three ways to use it:

1. Cursor on a table name: you are asked for an optional WHERE filter, and scripts are generated for the matching rows.

2. A SELECT query selected: the query is run and scripts are generated for the rows it returns. If the query uses several tables, you choose the target table.

3. From the results grid: select rows in the mssql results grid, copy them (Ctrl+C), then run Generate data scripts from copied results.

   The grid copies without headers unless mssql.copyIncludeHeaders is on. Both cases work: with headers the columns are matched by name; without them, by position when every column of the table was copied. If only some columns were copied, you are asked to match them one by one, and the warning offers to turn headers on for you. It also works without a connection, as long as the rows carry their headers (data types are then inferred from the text).

Example output:

-- Data scripts for [dbo].[Customers]
-- 2 row(s) · key: [CustomerId]
BEGIN TRAN;

-- ===== INSERT =====
SET IDENTITY_INSERT [dbo].[Customers] ON;
INSERT INTO [dbo].[Customers] ([CustomerId], [Name], [CreatedAt]) VALUES (1, N'O''Brien', '2024-01-15T09:30:00.000');
INSERT INTO [dbo].[Customers] ([CustomerId], [Name], [CreatedAt]) VALUES (2, NULL, NULL);
SET IDENTITY_INSERT [dbo].[Customers] OFF;

-- ===== UPDATE =====
UPDATE [dbo].[Customers] SET [Name] = N'O''Brien', [CreatedAt] = '2024-01-15T09:30:00.000' WHERE [CustomerId] = 1;

-- ===== DELETE =====
DELETE FROM [dbo].[Customers] WHERE [CustomerId] = 1;

-- Review the script and change ROLLBACK to COMMIT to apply the changes.
ROLLBACK;
-- COMMIT;

How the scripts are built:

• UPDATE and DELETE use the primary key (or the first unique index). If the table has none, all columns are compared and a warning is added.
• IDENTITY columns are inserted with SET IDENTITY_INSERT. Computed and rowversion columns are skipped.
• Values are written with the right literal for each type (N'…' for Unicode, numbers without quotes, 0x… for binary).
• datetime values use the ISO 8601 format, so the script works regardless of the session language or SET DATEFORMAT.
• Everything is wrapped in BEGIN TRAN … ROLLBACK so nothing changes until you decide to commit.
• Scripts are only opened in a new editor. They are never executed automatically.

Management panel

Open management panel shows everything in one screen:

• Auto-replacements tab: a searchable list (by token, description or SQL), an editor for the token, the description and the SQL text, and buttons to save, insert in the editor, duplicate or delete. The toolbar also has import, export, examples, the JSON file and reload.
• Connections tab: your mssql connections, with their environment, plus a box where you can paste a connection string.
• Environments tab: the rules that decide the environment of a connection — label, pattern, colour (picked from swatches, with a live preview) and whether it notifies. Rules can be reordered, and a Try it box tells you which rule would win for a given connection name.

Connections

The connections are the ones of the mssql extension, so nothing is duplicated and your existing profiles show up here. From the panel you can:

• Add from a connection string: paste it, press Read and the fields are filled in. The password is discarded: it is never written to settings. mssql asks for it the first time you connect and stores it in the system credential store.
• Edit the name, server, database, user, authentication and environment.
• Open a connected editor: opens a new SQL editor already connected to that profile. mssql only allows this through its newEditorConnectionBehavior setting, so the extension asks for permission before changing it; you can also just use the mssql picker.
• Copy the connection string (without the password) or delete the profile.

The environment you assign creates a connection group with a colour in mssql, so its own colouring matches too.

Environment indicator

Once you assign environments (or your server names match the default rules), the status bar shows where you are:

By default the environment is shown in the status bar. With tsqlToolbox.environment.display you can turn it into a sticky notification in the bottom-right corner (notification), or have both (both). The notification stays open while you are connected and is replaced when you switch connection, but it occupies the notification area and shows a spinner: VS Code gives extensions no other way to keep something in that corner.

Manage them in the Environments tab of the panel (Manage environments): add, edit, reorder and delete rules, pick the colour from swatches and check which rule wins with the Try it box. The first rule that matches is the one used, so keep the most specific ones at the top. Everything is stored in tsqlToolbox.environments, which you can still edit by hand.

The rules are regular expressions matched, ignoring case, against connection name · server · database. VS Code only allows a coloured background for red and yellow, so the other colours tint the text.

The indicator only appears once you have allowed the extension to use the mssql connections (run Enable environment indicator, or use any other database feature) and when a rule matches: if none does, nothing is shown. The environment is taken from the connection profile when it is saved in mssql, and from SERVERPROPERTY('ServerName') otherwise, so ad-hoc connections are detected too.

If you cannot see it, open the Environments tab: it shows the connection of the active editor, which rule wins and a button to turn the indicator on.

Missing WHERE warning

DELETE and UPDATE statements without a WHERE clause are underlined in the editor and listed in the Problems panel, before you run them.

DELETE FROM dbo.Customers;
~~~~~~~~~~~~~~~~~~~~~~~~~  DELETE without WHERE: every row in dbo.Customers will be deleted.

It ignores what only looks like a statement: comments, strings, column names such as [DELETE], ON DELETE CASCADE, trigger definitions (AFTER DELETE, IF UPDATE(Column)) and UPDATE STATISTICS.

It stays quiet when the rows are already restricted: a WHERE clause (even a multi-line one), a JOIN, a common table expression, a table variable or a temporary table. A WHERE that only appears inside a subquery does not count.

Both warnings can be turned off independently with tsqlToolbox.safety.warnDeleteWithoutWhere and tsqlToolbox.safety.warnUpdateWithoutWhere.

Requirements

• Auto-replacements work on their own.
• Go to Implementation and Data scripts use the connection of the Microsoft SQL Server (mssql) extension. The editor must be connected (MS SQL: Connect).
• The first time, mssql asks whether you allow this extension to use its connections. You can change that later with MS SQL: Edit Connection Sharing Permissions.

Note: Microsoft has marked the mssql connection sharing API as scheduled for retirement, and mssql may show a notice when it is used. While the API is available, these two features work normally. If it is removed, they will stop working; auto-replacements will keep working.

Security and privacy

• No telemetry, no network access, no third-party dependencies. The extension only talks to SQL Server through the mssql extension.
• Nothing is written to your database. The extension only runs SELECT statements and catalog queries; generated scripts are opened in an editor for you to review and run yourself.
• Queries are protected. Only a single read-only SELECT can be run from a selection or a filter (statements such as INTO, EXEC, SET, WAITFOR or multiple statements are rejected). As a second layer, every such query runs with a row limit inside a transaction that is always rolled back, on a temporary connection that mssql closes afterwards.
• Your credentials are never read. The extension only asks mssql for the connection of the active editor; it does not request connection strings or passwords.
• Generated scripts are never executed. They open in an unsaved editor for you to review.
• Your auto-replacements stay on your machine, in VS Code's storage or in the file you choose. The storage path can only be set in user settings, so a project cannot redirect where the extension writes.
• Be careful with shared snippet files: an auto-replacement inserts text that you might then run. Only use files from people you trust.
• Generated data scripts may contain sensitive data from your database. Treat them like any other data export.
• The clipboard is only read when you run the "from copied results" command.
• No background work. The extension has no timers: the environment indicator reacts to your own actions only (switching editor, moving the cursor at most once every five seconds, returning to the window). With VS Code in the background, nothing runs.
• One automatic query, and you can switch it off. The only query the extension sends on its own is SERVERPROPERTY('ServerName'), once per connection and only when the profile is not saved in mssql, cached for five minutes. Set tsqlToolbox.environment.detection to profile and no query is ever sent; off disables the indicator entirely.

Settings

Commands

All commands are in the Command Palette under T-SQL Toolbox.

Keyboard shortcuts: the only key this extension binds is Tab, to expand a token (and only when a token is right before the cursor; it can be disabled with tsqlToolbox.expandOnTab). No other shortcut is taken, so nothing of yours is overridden. To assign your own, open Keyboard Shortcuts, search for "T-SQL Toolbox" and pick the keys you prefer.

Known limitations

• The mssql results grid cannot be extended by other extensions, so data scripts from the grid go through the clipboard.
• mssql removes line breaks inside values when copying, unless you turn mssql.copyRemoveNewLine off.
• When copying from the grid, a cell containing the text NULL is treated as a real NULL.
• Very long values (over 65,535 characters) may arrive truncated from mssql; the script warns about it.
• CREATE OR ALTER requires SQL Server 2016 SP1 or later. For older servers, set scriptMode to alter.