sqlserver-live-knowledgebase

SQL Server Live Knowledge Base Builder

When to Use

Use this skill when:

• sqlserver-er-knowledgebase fails or produces incomplete results (no ER diagram files found, parse errors, stale diagrams)
• The project has no committed ER diagram files but has a live database
• You need the ground-truth schema from an actual SQL Server instance
• You need to bootstrap or refresh the project CLAUDE.md with live database knowledge

This skill is a fallback — prefer sqlserver-er-knowledgebase when diagram files are available and up to date.

HITL (Human In The Loop)

Ask the user before proceeding when:

• The .env file contains multiple SQL Server connection strings — ask which one to use
• The connection string targets a production-looking server (hostname contains prod, prd, live) — confirm intent
• The database contains more than 200 tables — ask whether to scope to specific schemas
• A schema or object type looks auto-generated or third-party (e.g. HangFire.*, __EFMigrationsHistory, AspNet*) — ask whether to include or exclude
• The CLAUDE.md diff is large (>100 lines changed) — show a summary and ask for approval before writing
• Any connection or permission error occurs — report the error and ask how to proceed

Never silently skip objects or make assumptions about scope. When confused, ask.

Prerequisites

Connection String

Read the SQL Server connection string from the project .env file. Look for these variable names in order:

SQLSERVER_CONNECTION_STRING
DB_CONNECTION_STRING
DATABASE_URL
ConnectionStrings__DefaultConnection
MSSQL_CONNECTION_STRING

If .env is not found, check these alternatives in order:

1. .env.local
2. .env.development
3. appsettings.Development.json → ConnectionStrings.DefaultConnection
4. appsettings.json → ConnectionStrings.DefaultConnection

If multiple connection strings are found, ask the user which one to use.

Tools

Requires sqlcmd or mssql-cli on PATH for query execution. If neither is available, fall back to docker exec against a running SQL Server container if one is detected.

# Check availability
which sqlcmd || which mssql-cli || docker ps --filter "ancestor=mcr.microsoft.com/mssql/server" --format "{{.Names}}"

Introspection Queries

Step 1 — Test Connectivity

SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, @@VERSION AS ServerVersion;

If this fails, report the error to the user and stop. Do not retry with modified credentials.

Step 2 — Schemas

SELECT
    s.name           AS SchemaName,
    s.schema_id      AS SchemaId,
    dp.name          AS OwnerName
FROM sys.schemas s
INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE s.name NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest')
ORDER BY s.name;

Step 3 — Tables and Columns

SELECT
    s.name                          AS SchemaName,
    t.name                          AS TableName,
    c.column_id                     AS ColumnOrdinal,
    c.name                          AS ColumnName,
    tp.name                         AS DataType,
    c.max_length                    AS MaxLength,
    c.precision                     AS Precision,
    c.scale                         AS Scale,
    c.is_nullable                   AS IsNullable,
    c.is_identity                   AS IsIdentity,
    dc.definition                   AS DefaultValue,
    ep.value                        AS ColumnDescription
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = t.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, c.column_id;

Step 4 — Primary Keys and Unique Constraints

SELECT
    s.name           AS SchemaName,
    t.name           AS TableName,
    kc.name          AS ConstraintName,
    kc.type_desc     AS ConstraintType,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS Columns
FROM sys.key_constraints kc
INNER JOIN sys.tables t ON kc.parent_object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.index_columns ic ON kc.unique_index_id = ic.index_id AND kc.parent_object_id = ic.object_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE t.is_ms_shipped = 0
GROUP BY s.name, t.name, kc.name, kc.type_desc
ORDER BY s.name, t.name, kc.name;

Step 5 — Foreign Keys

SELECT
    s.name                          AS SchemaName,
    tp.name                         AS ParentTable,
    fk.name                         AS ForeignKeyName,
    STRING_AGG(cp.name, ', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id) AS ParentColumns,
    rs.name                         AS ReferencedSchema,
    tr.name                         AS ReferencedTable,
    STRING_AGG(cr.name, ', ') WITHIN GROUP (ORDER BY fkc.constraint_column_id) AS ReferencedColumns,
    fk.delete_referential_action_desc AS OnDelete,
    fk.update_referential_action_desc AS OnUpdate
FROM sys.foreign_keys fk
INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN sys.schemas s ON tp.schema_id = s.schema_id
INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN sys.schemas rs ON tr.schema_id = rs.schema_id
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
INNER JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE tp.is_ms_shipped = 0
GROUP BY s.name, tp.name, fk.name, rs.name, tr.name, fk.delete_referential_action_desc, fk.update_referential_action_desc
ORDER BY s.name, tp.name, fk.name;

Step 6 — Indexes

SELECT
    s.name                          AS SchemaName,
    t.name                          AS TableName,
    i.name                          AS IndexName,
    i.type_desc                     AS IndexType,
    i.is_unique                     AS IsUnique,
    i.filter_definition             AS FilterExpression,
    STRING_AGG(CASE WHEN ic.is_included_column = 0 THEN c.name END, ', ')
        WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns,
    STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name END, ', ')
        WITHIN GROUP (ORDER BY ic.index_column_id) AS IncludedColumns
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE t.is_ms_shipped = 0 AND i.is_primary_key = 0 AND i.type > 0
GROUP BY s.name, t.name, i.name, i.type_desc, i.is_unique, i.filter_definition
ORDER BY s.name, t.name, i.name;

Step 7 — Check Constraints

SELECT
    s.name           AS SchemaName,
    t.name           AS TableName,
    cc.name          AS ConstraintName,
    cc.definition    AS Expression
FROM sys.check_constraints cc
INNER JOIN sys.tables t ON cc.parent_object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, cc.name;

Step 8 — Views

SELECT
    s.name           AS SchemaName,
    v.name           AS ViewName,
    m.definition     AS ViewDefinition
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
INNER JOIN sys.sql_modules m ON v.object_id = m.object_id
WHERE v.is_ms_shipped = 0
ORDER BY s.name, v.name;

Step 9 — Stored Procedures

SELECT
    s.name           AS SchemaName,
    p.name           AS ProcedureName,
    m.definition     AS ProcedureDefinition,
    STRING_AGG(
        pa.name + ' ' + tp.name +
        CASE WHEN tp.name IN ('varchar','nvarchar','char','nchar') THEN '(' + CAST(pa.max_length AS VARCHAR) + ')' ELSE '' END +
        CASE WHEN pa.has_default_value = 1 THEN ' = ' + CAST(pa.default_value AS NVARCHAR(MAX)) ELSE '' END +
        CASE WHEN pa.is_output = 1 THEN ' OUTPUT' ELSE '' END,
        ', '
    ) WITHIN GROUP (ORDER BY pa.parameter_id) AS Parameters
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
INNER JOIN sys.sql_modules m ON p.object_id = m.object_id
LEFT JOIN sys.parameters pa ON p.object_id = pa.object_id AND pa.parameter_id > 0
LEFT JOIN sys.types tp ON pa.user_type_id = tp.user_type_id
WHERE p.is_ms_shipped = 0
GROUP BY s.name, p.name, m.definition
ORDER BY s.name, p.name;

Step 10 — Functions (Scalar + Table-Valued)

SELECT
    s.name           AS SchemaName,
    o.name           AS FunctionName,
    o.type_desc      AS FunctionType,
    m.definition     AS FunctionDefinition,
    STRING_AGG(
        pa.name + ' ' + tp.name +
        CASE WHEN tp.name IN ('varchar','nvarchar','char','nchar') THEN '(' + CAST(pa.max_length AS VARCHAR) + ')' ELSE '' END,
        ', '
    ) WITHIN GROUP (ORDER BY pa.parameter_id) AS Parameters
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
LEFT JOIN sys.parameters pa ON o.object_id = pa.object_id AND pa.parameter_id > 0
LEFT JOIN sys.types tp ON pa.user_type_id = tp.user_type_id
WHERE o.type IN ('FN', 'IF', 'TF') AND o.is_ms_shipped = 0
GROUP BY s.name, o.name, o.type_desc, m.definition
ORDER BY s.name, o.name;

Step 11 — Triggers

SELECT
    s.name           AS SchemaName,
    t.name           AS TableName,
    tr.name          AS TriggerName,
    tr.is_disabled   AS IsDisabled,
    te.type_desc     AS EventType,
    m.definition     AS TriggerDefinition
FROM sys.triggers tr
INNER JOIN sys.tables t ON tr.parent_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.sql_modules m ON tr.object_id = m.object_id
INNER JOIN sys.trigger_events te ON tr.object_id = te.object_id
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, tr.name;

Step 12 — Sequences

SELECT
    s.name           AS SchemaName,
    seq.name         AS SequenceName,
    tp.name          AS DataType,
    seq.start_value  AS StartValue,
    seq.increment    AS IncrementBy,
    seq.minimum_value AS MinValue,
    seq.maximum_value AS MaxValue,
    seq.is_cycling   AS IsCycling
FROM sys.sequences seq
INNER JOIN sys.schemas s ON seq.schema_id = s.schema_id
INNER JOIN sys.types tp ON seq.user_type_id = tp.user_type_id
ORDER BY s.name, seq.name;

Step 13 — Synonyms

SELECT
    s.name              AS SchemaName,
    syn.name            AS SynonymName,
    syn.base_object_name AS TargetObject
FROM sys.synonyms syn
INNER JOIN sys.schemas s ON syn.schema_id = s.schema_id
ORDER BY s.name, syn.name;

Step 14 — Row Counts (for context)

SELECT
    s.name           AS SchemaName,
    t.name           AS TableName,
    p.rows           AS ApproxRowCount
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0, 1)
WHERE t.is_ms_shipped = 0
ORDER BY p.rows DESC;

CLAUDE.md Generation

Target File

Write to CLAUDE.md in the project root (the repository root, not the plugin directory).

Structure

Generate a ## Database Schema section with the following sub-sections:

## Database Schema

Auto-generated from live SQL Server introspection.
Server: {ServerName} | Database: {DatabaseName} | Version: {ServerVersion}
Last refreshed: {timestamp}

### Schemas

| Schema | Owner | Purpose |
|---|---|---|
| dbo | dbo | Core domain tables |
| auth | dbo | Authentication and authorization |

### Tables

#### dbo.Orders

| Column | Type | Nullable | Default | Key | Description |
|---|---|---|---|---|---|
| Id | int | No | identity | PK | |
| CustomerId | int | No | — | FK → dbo.Customers.Id | |
| Total | decimal(18,2) | No | 0 | — | |
| Status | nvarchar(50) | No | 'Pending' | — | |
| CreatedAt | datetime2(7) | No | SYSUTCDATETIME() | — | |

Indexes: IX_Orders_CustomerId (CustomerId) INCLUDE (CustomerName, Total)
Constraints: CK_Orders_Total ([Total] >= 0)
Approx rows: 150,000

#### dbo.OrderItems
...

### Views

| View | Schema | Description |
|---|---|---|
| vw_ActiveCustomers | dbo | Customers with non-cancelled orders + order count |

### Stored Procedures

| Procedure | Schema | Parameters | Description |
|---|---|---|---|
| usp_GetOrderById | dbo | @OrderId INT | Fetch single order by PK |
| usp_CreateOrder | dbo | @CustomerName NVARCHAR(200), @Total DECIMAL(18,2) | Insert new order |

### Functions

| Function | Schema | Type | Parameters | Description |
|---|---|---|---|---|
| fn_CalculateTotal | dbo | Scalar | @OrderId INT | Sum line items for order |

### Triggers

| Trigger | Schema.Table | Event | Enabled | Description |
|---|---|---|---|---|
| trg_Orders_Audit | dbo.Orders | AFTER INSERT, UPDATE | Yes | Writes to dbo.AuditLog |

### Sequences

| Sequence | Schema | Type | Start | Increment |
|---|---|---|---|---|
| OrderNumberSeq | dbo | bigint | 1000 | 1 |

### Synonyms

| Synonym | Schema | Target |
|---|---|---|
| RemoteProducts | dbo | LinkedServer.Catalog.dbo.Products |

### Relationships

Orders ||--o{ OrderItems : "FK_OrderItems_Orders (CASCADE)" Customers ||--o{ Orders : "FK_Orders_Customers (NO ACTION)"

Description Inference

For the Description column in tables, procedures, functions, and triggers:

1. Use MS_Description extended properties if present
2. Otherwise, infer a short description from the object name and definition
3. If ambiguous, leave blank — do not guess

CLAUDE.md Comparison and Update

If CLAUDE.md Does Not Exist

1. Generate the full ## Database Schema section
2. Write it to CLAUDE.md in the project root
3. Stage and commit with message: docs: add database schema to CLAUDE.md from live introspection

If CLAUDE.md Exists

1. Read the existing file
2. Locate the ## Database Schema section (between ## Database Schema and the next ## heading or EOF)
3. Compare the existing schema section with the newly introspected one
4. Classify changes:

Change Type	Example
New object	Table, column, view, proc, function, trigger, sequence, or synonym added
Removed object	Object present in CLAUDE.md but absent in live DB
Modified object	Column type changed, new parameter on proc, index added/dropped
No change	Section is identical

5. If no changes — report "CLAUDE.md is up to date" and stop
6. If changes exist — produce a diff summary:

CLAUDE.md Schema Diff
=====================
Added:    dbo.Shipments (table, 8 columns)
          dbo.usp_GetShipmentsByOrder (procedure)
Modified: dbo.Orders — added column [ShippedAt datetime2(7) NULL]
          dbo.usp_CreateOrder — added parameter @ShippingAddress NVARCHAR(500)
Removed:  dbo.fn_LegacyCalc (function)
          IX_Orders_Status (index on dbo.Orders)

7. HITL gate — show the diff summary to the user and ask:

   • "Apply all changes?"
   • "Apply selectively?" (let user pick which changes to include)
   • "Skip update?"

8. On approval, replace the ## Database Schema section in CLAUDE.md with the new version. Preserve all other sections.

9. Stage and commit with message: docs: update database schema in CLAUDE.md from live introspection

Merge Safety

• Never overwrite non-schema sections of CLAUDE.md
• If the ## Database Schema heading is not found in an existing CLAUDE.md, append the section at the end
• If CLAUDE.md has uncommitted changes, ask the user before modifying

Exclusion Filters

By default, exclude these objects from the knowledge base (they add noise):

Pattern	Reason
__EFMigrationsHistory	EF Core internal
sysdiagrams	SSMS diagram metadata
HangFire.*	Background job framework
AspNet* tables	ASP.NET Identity defaults (include only if the project uses Identity)
dt_* procs	Legacy SSMS support procs
sp_MSforeach*	System utility procs

If any excluded object appears to be project-relevant based on FK references from non-excluded tables, ask the user whether to include it.

Custom exclusions can be configured in .db-agent.json:

{
  "engines": {
    "sqlserver": {
      "liveKnowledgebase": {
        "connectionStringEnvVar": "SQLSERVER_CONNECTION_STRING",
        "excludeSchemas": ["HangFire", "staging"],
        "excludeTables": ["__EFMigrationsHistory", "sysdiagrams"],
        "includeDefinitions": false
      }
    }
  }
}

Definition Inclusion

By default, store procedure/function/trigger/view definitions (full SQL body) are not written to CLAUDE.md — only signatures and parameter lists. This keeps CLAUDE.md concise.

Set includeDefinitions: true in config to include full definitions, or the user can ask explicitly.

Error Handling

Error	Action
.env not found / no connection string	Report to user, list checked locations, ask for connection string
Connection refused / timeout	Report error, suggest checking server status and firewall, ask user
Login failed	Report error, suggest verifying credentials in .env, ask user
Permission denied on sys.* views	Report which views failed, offer partial introspection of accessible objects, ask user
Query timeout on large database	Retry with SET LOCK_TIMEOUT 30000, offer schema-scoped introspection, ask user
sqlcmd / mssql-cli not found	Report missing tool, provide install instructions, ask user

Never retry with different credentials. Never modify .env. Always report and ask.

Security

• Read-only — this skill only runs SELECT queries against sys.* catalog views. It never executes DDL, DML, or any modifying statement.
• Credential handling — connection strings are read from .env but never logged, written to knowledge base files, or included in CLAUDE.md output. The server name and database name (not credentials) appear in the metadata header.
• Production safeguard — if the connection string or server name contains prod, prd, live, or production, require explicit HITL confirmation before proceeding.