DB Documenter

Reverse-engineers a SQL Server database into ER diagrams + AI-generated documentation.

What it produces

output/
├── DATABASE_DOCUMENTATION.md   ← everything in one file (Mermaid diagrams + all object docs)
├── er_diagram.mmd              ← ER diagram (Mermaid) — paste into https://mermaid.live
├── upstream_lineage.mmd        ← external systems → this DB (linked servers, cross-DB, OPENQUERY)
├── upstream_sources.md         ← linked servers, synonyms, agent jobs, SSIS, code patterns,
│                                 and tables likely loaded by external processes
├── dependency_graph.mmd        ← proc/view → table dependency flowchart
├── schema_overview.md          ← table list with column counts + row counts
├── objects/                    ← one markdown doc per proc/view/function/trigger
├── metadata.json               ← raw extracted metadata (reusable)
└── .cache/                     ← AI analysis cache (resume-safe)

Upstream detection — what it finds and what it can’t

Found automatically from inside SQL Server:

• Linked servers and what they point to
• Cross-database / cross-server references in code
• Synonyms pointing to other databases
• OPENQUERY / OPENROWSET / OPENDATASOURCE / BULK INSERT / four-part names in procs
• SQL Agent job steps (needs msdb read access)
• SSIS packages (needs SSISDB access)
• “Entry-point tables”: tables no internal code references — flagged as likely loaded externally (ETL tool, app layer, API, Power Automate)

The per-proc AI analysis also has a dedicated “Upstream / External Data Sources” section that infers source systems from naming and code patterns.

NOT visible from inside the DB: external ETL tools writing directly, application-layer inserts, API pushes. The entry-point table list is your checklist to confirm those with the integration/app teams.

Each object doc contains: Purpose, Parameters, Tables Read, Tables Written, Logic Walkthrough, Business Rules, Risks & Observations.

Setup (Mac)

# 1. ODBC driver (if not already installed for Azure Data Studio work)
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew install msodbcsql18 unixodbc

# 2. Python deps
pip install pyodbc anthropic

# 3. API key
export ANTHROPIC_API_KEY=sk-ant-...

Run

# Full run (extract + AI analysis + docs)
python db_documenter.py --database MyDB --server localhost --user sa --password 'YourPass'

# Diagrams + schema only, no AI (fast, free)
python db_documenter.py --database MyDB --password 'YourPass' --skip-ai

# Docker SQL Server on a non-default port
python db_documenter.py --database MyDB --server "localhost,1433" --password 'YourPass'

Notes

• Resume-safe: if the run dies midway (network, rate limits), re-run it. Already-analyzed objects are cached in output/.cache/ and skipped.
• Cost control: each proc/view = 1 API call. A DB with 200 procs ≈ 200 calls. Run --skip-ai first to see what you’re dealing with.
• Missing FK constraints: if the legacy DB has no foreign keys defined, the ER diagram will show tables but few relationships. The AI proc analysis (“Tables Read/Written”) helps you infer the real relationships from join logic.
• Rendering Mermaid: paste .mmd content into https://mermaid.live, or view DATABASE_DOCUMENTATION.md in VS Code with the Mermaid preview extension.
• Definitions over 150k chars are truncated before sending to the API.