Excavate a Legacy Stored-Procedure Estate with Long-Context AI

Tier: Core · Workflow area: Migration & Modernization · ~40 minutes

Rewriting a legacy procedure without understanding it is how migrations lose the business rules that only existed in code. This playbook is the excavation pass: you feed the model the complete source plus its call graph, and extract a structured dossier — what the procedure does, what it depends on, what is dead, and which behaviors look like bugs preserved as features. The output is a review document, not a rewrite; the rewrite comes later, and only after a human confirms the dossier.

When to use

• Pre-migration inventorybefore an Oracle-to-Postgres or Sybase retirement project quotes an effort estimate, every thousand-line package needs a dossier. AI turns weeks of reading into days of verifying.
• The departed-author problemthe procedure works, the author is gone, the ticket says "small change." You need the business-rule map before touching anything.
• Onboarding onto an inherited estatea new DBA or consultant can build a mental model of fifty procedures in a week instead of a quarter.

When NOT to use

• As a substitute for testinga beautiful AI explanation of what the code appears to do is not evidence of what it does. The dossier guides test design; it never replaces execution against real data.
• When the source embeds secrets or production datahardcoded passwords, API keys and literal customer values inside old procedures are common. Scrub before pasting, or use a tenant-approved model.
• Trivial proceduresa 30-line wrapper needs a read, not an excavation. Save the ritual for code above roughly 300 lines or anything with dynamic SQL.

The recipe

1. 1.

   Extract the complete source plus everything it touches. Partial context produces confident wrong summaries. Pull the procedure and its direct dependencies:

   Copy

   -- SQL Server: procedure source
   SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.usp_ProcessMonthlyBilling'));
   
   -- and its dependency edges
   SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name
   FROM sys.dm_sql_referenced_entities('dbo.usp_ProcessMonthlyBilling', 'OBJECT');
   

   Copy

   -- Oracle: package body plus dependencies
   SELECT text FROM all_source
   WHERE name = 'PKG_BILLING' AND type = 'PACKAGE BODY' ORDER BY line;
   
   SELECT referenced_owner, referenced_name, referenced_type
   FROM all_dependencies WHERE name = 'PKG_BILLING';
   

   Also grab the DDL of every table the dependency query names — column types and constraints are half the business logic.

2. 2.

   Ask for a structured dossier, not a summary. A free-form "explain this code" prompt yields a book report. Demand sections with line references:

   Copy

   You are analyzing a legacy T-SQL stored procedure (source and table DDL
   below). Produce an excavation dossier with EXACTLY these sections:
   
   1. PURPOSE — one paragraph: what business process this implements.
   2. CONTROL FLOW — the major branches as a numbered outline, each with
      line ranges and the condition that selects it.
   3. BUSINESS RULES — every rule encoded in code (thresholds, statuses,
      date cutoffs, rounding), each with its line number and the literal
      values involved.
   4. SIDE EFFECTS — every table written, message sent, file touched, or
      transaction controlled, with line numbers.
   5. DEAD OR SUSPECT CODE — branches that appear unreachable, variables
      never read, comparisons that can never be true. Mark each SUSPECT
      unless you can prove it dead from this source alone.
   6. DEPENDENCY MAP — objects called and objects calling in, as a list.
   7. QUESTIONS FOR THE OWNER — everything you could not determine.
   
   Cite line numbers for every claim. If a section is empty, say so.
   Do not propose a rewrite.
   
   [paste source + DDL + dependency list]
   

   The line-number requirement is the verification hook: every claim becomes checkable in seconds.

3. 3.

   Spot-check the dossier against the source — ten claims minimum. Pick three business rules, three side effects, two dead-code claims and two control-flow branches, and read the cited lines yourself. A typical finding worth catching: the model reports "orders under 100 are exempt" when line 412 actually reads @total <= 100 — boundary semantics matter in billing code.

4. 4.

   Interrogate the suspect branches with data. Dead-code claims are hypotheses. Test them against production reality:

   Copy

   -- Model claims the status 'H' branch (lines 610-680) is dead.
   -- Does 'H' still occur?
   SELECT status, COUNT(*), MAX(updated_at)
   FROM dbo.billing_queue GROUP BY status;
   

   Sample output that settles it: H | 4,210 | 2019-03-31 — the branch is dormant, not dead, and the 2019 date is a migration clue worth chasing.

5. 5.

   Map the blast radius from the database's side. The model only sees the code you pasted. Confirm inbound callers independently — jobs, triggers and app code the dossier cannot know about:

   Copy

   -- SQL Server Agent jobs that call it
   SELECT j.name, s.step_name FROM msdb.dbo.sysjobs j
   JOIN msdb.dbo.sysjobsteps s ON s.job_id = j.job_id
   WHERE s.command LIKE '%usp_ProcessMonthlyBilling%';
   

   Then grep the application repos for the procedure name. Every caller found goes into the dossier's dependency map by hand.

6. 6.

   Publish the dossier with a verification ledger. The document that enters the migration wiki carries a header: AI-drafted 2026-08-06; 10 claims spot-checked, 1 corrected (boundary at line 412); suspect branches tested against data; inbound callers verified independently. Now it is evidence, not folklore.

Make it yours

• Oracle PL/SQLpackages hide state in package-level variables; ask the dossier to list them explicitly, and flag any autonomous transactions and WHEN OTHERS THEN NULL handlers as first-class findings.
• PostgreSQL PL/pgSQLdependency metadata is thinner; supplement pg_depend with a grep across dump files, and have the model flag dynamic SQL (EXECUTE format(...)) whose targets metadata cannot see.
• Very large procedurespast roughly 3,000 lines, chunk by top-level branch, run the dossier prompt per chunk with the shared header context, then a final pass to merge — and re-verify claims that span chunk boundaries, where models stitch badly.

Quality check

• Every dossier claim cites a line numberan uncited claim is a guess wearing a suit. Reject sections that summarize without pointing.
• Dead-code verdicts were tested against datathe model can prove reachability from source sometimes; it can never prove a status value stopped occurring. Only queries prove that.
• Inbound callers came from your queries, not the modelthe pasted source cannot reveal who calls it. Jobs, triggers and app-repo grep are your responsibility.
• The "questions for the owner" section got answeredthose questions are the model telling you where its understanding ends. Shipping the dossier with them unanswered ships known blind spots.