sql-introspection

name	sql-introspection
description	Connect to a SQL database (SQL Server, PostgreSQL, MySQL, etc.) for read-only introspection — listing databases, inspecting columns/keys, sampling rows. Read-only by default; DDL/DML needs explicit per-conversation approval and ships as a repo script with rollback. Use for any app/project whenever a task needs to query, introspect, or verify against the database.

SQL — Database Introspection

Access the SQL database over Windows auth or other authentication systems using the sqlcmd ODBC client (or target-specific clients like psql for Postgres, or mysql for MySQL). Default posture is read-only introspection.

Connect (SQL Server / sqlcmd Example)

On Windows systems, sqlcmd often lives at C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd or is available in the system PATH. Run it through the Bash tool or command line.

Base invocation:

sqlcmd -S <server_address> -E -C -l 15 -Q "SET NOCOUNT ON; SELECT SUSER_SNAME() AS lgn, @@VERSION AS ver;"

Flag cheatsheet:

• -S <server_address> — database server hostname/address (and optional \Instance or ,port).
• -E — trusted connection (Windows auth, current user). Do not hardcode credentials.
• -C — trust the server certificate (required if using self-signed or unverified certificates).
• -l 15 — login timeout in seconds (fail fast instead of hanging).
• -Q "…" — run query and exit (avoid using -q which stays interactive and can hang execution).

Clean, parseable output for introspection:

• -s "|" column separator · -W trim whitespace · -h -1 no column-header rule.
• Start the batch with SET NOCOUNT ON; to drop unnecessary "(N rows affected)" logs.
• Pipe through head to cap large results.

sqlcmd -S <server_address> -E -C -l 15 -s "|" -W -h -1 -Q "SET NOCOUNT ON; <query>" 2>&1 | head -200

Read-only by default (data safety)

Per project safety rules: introspection is always fine without asking — SELECT against catalog views, INFORMATION_SCHEMA, sys.*, and sampling table rows. Never run DDL or data-mutating SQL (CREATE/ALTER/DROP/INSERT/UPDATE/DELETE/MERGE/TRUNCATE, or metadata procedures that perform writes) without explicit approval in the current conversation. When a change is wanted, propose it as a file under the repo's _migration/ (or backend/sql/) with a header stating exactly what running it does and how to roll back — do not execute it directly.

Introspection recipes (T-SQL)

List user databases:

SELECT name FROM sys.databases WHERE database_id > 4 ORDER BY name;

Columns of a table (cross-database via 3-part name — INFORMATION_SCHEMA is per-DB):

SELECT TABLE_NAME, ORDINAL_POSITION AS pos, COLUMN_NAME, DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH AS len, IS_NULLABLE AS nul
FROM <database_name>.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('tblX','tblY')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Primary / foreign keys:

SELECT t.name AS tbl, c.name AS col, k.name AS keyname, k.type_desc
FROM <database_name>.sys.key_constraints k
JOIN <database_name>.sys.tables t ON t.object_id = k.parent_object_id
JOIN <database_name>.sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = k.unique_index_id
JOIN <database_name>.sys.columns c ON c.object_id = t.object_id AND c.column_id = ic.column_id
WHERE t.name = 'tblX';

Find a table/view across a DB; sample rows safely:

SELECT TOP 25 * FROM <database_name>.dbo.<table> ORDER BY 1;     -- always TOP-limit samples

Orienting in an unfamiliar database

Start broad, then drill down — do not assume table names:

1. List databases (recipe above) to find the right one.
2. List its tables/views.
3. Inspect columns + keys for the candidates (recipes above).
4. Sample a few TOP 25 rows to learn the data shape and any lookup codes.

Cross-database joins/views resolve as long as the databases live on the same server — use 3-part names (<database_name>.dbo.table).

Verification

• Connected as the expected login (SELECT SUSER_SNAME()), targeting the correct server address.
• Used appropriate connection parameters (no inline plaintext credentials).
• Query was read-only (introspection or TOP-limited sample) — or, if a change, it was approved in the current conversation and delivered as a script, not executed directly.

Gotchas

• Omit -C → SSL Provider / certificate chain login error on systems requiring certificate trusts.
• Use -Q (run-and-exit), never -q (interactive — hangs command tools).
• INFORMATION_SCHEMA is scoped to the connected DB; for another DB use a 3-part name (<database_name>.INFORMATION_SCHEMA.COLUMNS) or USE <db>; first.
• A bare server name resolves to the default instance; if a named instance/port is required, use -S <server_address>\Instance or -S <server_address>,1433.