sqlglass

What a query touches: tables/views, columns per table, CTEs, parameters and its output columns.

Generate a CREATE TABLE script (TEXT ONLY -- this server never runs DDL; the user reviews and runs it). Checked against the cached schema: the name must be free, foreign keys must point at a real primary/unique key with the same column types, FK columns get an index, constraints get conventional names, and the script is wrapped in IF OBJECT_ID(...) IS NULL so it can be re-run. Returns sql + undo_sql + notes. name: "dbo.VendorScore" columns: [{"name": "VendorScoreId", "type": "int", "identity": true}, {"name": "VendorId", "type": "int", "nullable": false}, {"name": "Score", "type": "decimal(5,2)", "nullable": false, "default": "0", "description": "0-100"}] primary_key: ["VendorScoreId"] foreign_keys: [{"columns": ["VendorId"], "references": "dbo.Vendor"}] (ref_columns default to the parent's primary key) indexes: [{"columns": ["ScoredOn"], "include": ["Score"], "unique": false}]

Generate a CREATE OR ALTER PROCEDURE script (TEXT ONLY -- never executed here) around a query. Give inline 'sql', or a library 'query' -- then its header params (name, type, default) become the procedure's parameters automatically. Every @variable in the body must be a typed parameter. params: [{"name": "@StartDate", "type": "date", "default": "'2026-01-01'", "description": "first order date"}] Returns sql + undo_sql + notes (including an EXEC example).

Generate a SELECT from the cached schema: names validated, joins inferred from foreign keys (bridge tables added automatically), aliases assigned, GROUP BY derived. Returns SQL text only; nothing is executed. tables: ["dbo.PoHeader", "dbo.Vendor"] (first = FROM; the rest are joined) columns: ["Vendor.Name", "PoHeader.OrderDate"] (Table.Column, or a bare Column when unambiguous) aggregates: [{"fn": "SUM", "column": "PoLine.Amount", "alias": "Total"}] fn: SUM COUNT COUNT_DISTINCT AVG MIN MAX filters: ["PoHeader.OrderDate >= @Start", "Vendor.Country = 'US'"] (ANDed; use @params for values) order_by: ["Total DESC"] top: 50 join_type: INNER | LEFT

Generate a CREATE OR ALTER VIEW script (TEXT ONLY -- never executed here) from a SELECT or a library query. Refuses what a view cannot contain: parameters, DECLARE, ORDER BY without TOP.

Remove a query from the library. Its text is kept in a snapshot, so restore_snapshot brings it back.

Columns (type, nullability, identity/computed), primary key, indexes, foreign keys in both directions and row count of one table or view. Always do this before writing SQL against a table.

Estimated execution plan, summarised: the expensive operators, scans on big tables, key lookups, sorts, implicit conversions, optimizer warnings and missing-index suggestions. The query is compiled, NOT executed, so this is safe on heavy queries. (SQL Server login needs the SHOWPLAN permission.)

Turn a hard-coded value in a saved query into a parameter: every occurrence of the literal ('2026-01-01', 100) becomes @param, and a '-- param:' header line is added with the old value as its default.

How two tables relate: the shortest chain of declared foreign keys between them, as ready-to-use JOIN lines.

Impact analysis: which saved queries read a given table/view (and optionally a given column of it). Ask this before a table or column is changed, renamed or retired.

Pretty-print T-SQL in one canonical style. Normalises cosmetics (adds AS to aliases, rewrites '[x] = expr' aliases to 'expr AS [x]'); meaning is unchanged. Comments inside expressions may move.

One saved query: header fields, parameters, the SQL, what tables it touches, and lint findings.

Lint every saved query (against each query's cached connection schema when available). Catches queries broken by a schema change: run it after refresh_schema.

Check a query for correctness traps (NOT IN + NULLs, LEFT JOIN turned INNER by WHERE, join without ON, TOP without ORDER BY), performance problems (functions on filtered columns, SELECT *, NOLOCK, leading-wildcard LIKE) and style. With a connection whose schema is cached, also verifies every table and column exists.

List the configured database connections (from sqlglass.toml), which is the default, where the query library lives, and whether each connection has a cached schema.

Browse the saved-query library. 'search' matches id, name, description and the SQL text; 'tag' filters by tag.

Before-images taken automatically ahead of every library write, newest first.

List tables and views with row counts and descriptions. Filter by schema name and/or a name pattern ('*invoice*'). For a big database prefer search_schema.

Dry-run a write WITHOUT writing: converts one UPDATE / DELETE / INSERT into the read-only SELECT that shows what it would do, plus a COUNT(*) of affected rows. The write statement itself is never sent to the database. UPDATE -> key columns + each SET column as '[col (current)]' / '[col (new)]' (changed_only=true hides no-op rows) DELETE -> the rows that would be removed (and which child tables reference them) INSERT -> the rows that would be added, under the target's column names run=true also executes the preview (read-only, row-capped) and returns the affected-row count and first rows. This server cannot apply the write; hand the reviewed statement to the user to run themselves.

Per-column row count, NULLs, distinct values, min and max, in one aggregate query. Use it to learn a column's grain and range before filtering or joining on it. Up to 15 columns per call.

Re-read tables, views, columns, keys, indexes and foreign keys from the database into the local cache. Run this when a table or column seems to be missing, or after the database changed.

Follow a database rename through every saved query, token-aware (strings and comments untouched). kind='table': old='dbo.Vendor', new='dbo.Supplier' kind='column': table='dbo.Vendor', old='Name', new='VendorName' (alias-qualified references, and bare ones in single-table queries; ambiguous bare references are reported, not changed). dry_run defaults to TRUE: review the diff, then call again with dry_run=false.

Put the queries in a snapshot back to how they were before that write ('latest' = undo the last write). The restore is itself snapshotted, so it can be undone too.

Run a read-only query and return the first rows. Give either inline 'sql' or a library 'query' (id or name). params: {"@Start": "2026-01-01"}; library queries fall back to their header defaults. Anything but SELECT/WITH is refused before reaching the database. Rows are capped at the connection's max_rows (lower it with max_rows=); to look at big data, aggregate in SQL.

A few rows of a table or view, to see what the values actually look like.

Save a query to the library as <id>.sql (id may contain folders: 'purchasing/open-pos-by-vendor'). sql is the body only -- do not DECLARE the parameters in it; describe them in params: [{"name": "@Start", "type": "date", "default": "'2026-01-01'", "description": "first order date"}] Updating an existing query needs overwrite=true; header fields left empty keep their current value. kind='script' stores DDL text from build_create_table / build_procedure / build_view (e.g. id 'ddl/usp_open_pos'): kept and versioned with the queries, but never linted as a query and never executed.

Find tables and columns whose name contains the text (or matches a * ? wildcard pattern), plus tables whose description mentions it. The way to locate data in an unfamiliar database.

Translate a query between dialects (tsql, postgres, mysql, sqlite, snowflake, bigquery, databricks, oracle, duckdb, redshift): TOP<->LIMIT, ISNULL/COALESCE, GETDATE, DATEADD, string functions, quoting.