SQL Object Impact Analysis

SQL Object Impact Analysis

You are performing a pre-change dependency trace for a database object (table, column, stored procedure, view, function, or trigger) inside a legacy or unfamiliar codebase. The goal is a single, trustworthy answer to: "If I change this, what else is affected?"

When to use this

Trigger when the user:

• Names a specific table, column, stored procedure, view, or trigger and asks what uses it, what depends on it, or whether it's safe to change/rename/drop

• Is about to modify a schema element in a codebase with no ORM, or a mixed legacy stack (raw SQL + stored procs + hand-written data access code)

• Asks for a "blast radius," "impact analysis," or "dependency trace" of a specific DB object

Do not use this for:

• General SQL performance tuning (use sql-optimization)

• SQL code quality/security review (use sql-code-review)

• Privacy/PII exposure analysis (use data-breach-blast-radius)

• Architecture-level documentation of an entire codebase (use doc-and-modernize)

Process

1. Confirm the target object and its exact name(s). Ask if ambiguous (e.g., multiple tables with similar names, or the user says "the customer table" without specifying schema). Get the precise identifier before searching.

2. Search the SQL layer first:

   • Search all .sql files, stored procedure definitions, views, functions, and triggers in the repo/database scripts folder for direct references to the object name

   • For a column: check every proc/view/trigger that selects, inserts, updates, or filters on it

   • For a table: check every proc/view/trigger that references it, plus foreign key relationships to/from other tables

   • For a stored procedure: check every other proc that calls it, and every scheduled job/agent step that invokes it

   • Note indirect references too — dynamic SQL (EXEC(@sql)), synonyms, and views built on views

3. Search the application layer:

   • Search all application code (C#, Java, JS/TS, Angular, whatever the repo contains) for:

     • Direct SQL strings referencing the object name

     • ORM/data-access-layer method or class names that map to it (e.g., a repository method calling the stored procedure)

     • API endpoint handlers that ultimately call into the affected data path

   • Trace one layer further where reasonable: does a frontend component consume an endpoint that touches this object? Note it, but don't chase every UI consumer exhaustively — flag "further downstream consumers likely exist" if the trail runs cold rather than guessing.

4. Classify each finding by confidence:

   • Direct — object name found verbatim in code/SQL

   • Indirect — reached via a proc call chain, ORM mapping, or dynamic SQL that couldn't be fully resolved statically

   • Uncertain — plausible but unverified (e.g., dynamic SQL construction that couldn't be traced to a literal object name)

   • Never present an Uncertain finding as if it were Direct — flag it clearly so the reader knows to verify manually

5. Produce the report (see Output Format below).

Output Format

Always structure the report as:

1. Target Object — exact name, type (table/column/proc/view/trigger), and schema

2. Direct SQL Dependents — list of procs/views/triggers/functions that reference it directly, grouped by object type

3. Application Code Dependents — list of files/classes/methods that reference it, grouped by layer (data access, API/service, frontend), with file paths

4. Indirect / Dynamic References — anything found via dynamic SQL, synonyms, or call chains that couldn't be fully resolved — labeled clearly as needing manual verification

5. Risk Summary — one paragraph: is this object narrowly used (low risk) or widely fanned-out (high risk)? Call out anything that touches a scheduled job, external integration, or reporting layer specifically, since those often get missed

6. Suggested Verification Steps — concrete next actions before making the change (e.g., "run these two procs against staging data first," "check if the ReportingService endpoint at X depends on this column")

Guidelines

• Never claim a dependency is complete or exhaustive — static text search cannot catch every dynamic SQL construction or reflection-based ORM mapping. State the search method used and its limits explicitly in the report.

• Never fabricate a file path, proc name, or line reference. If you searched and found nothing, say "no references found via text search" — not silence, and not an invented result.

• If the codebase is large enough that a full trace isn't feasible in one pass, say so and propose scoping (e.g., "search only the Orders module first") rather than silently producing a partial report as if it were complete.

• Keep the report scannable — this is a pre-change safety check someone will read in a few minutes before making a decision, not a full architecture document.