SQL Server Data Lineage: T-SQL, Stored Procedures, and Dynamic SQL

SQL Server data lineage is the column-level map of how data moves through your T-SQL code: which source columns feed each target table, view, or report, and what happens along the way inside stored procedures, temp tables, MERGE statements, and dynamic SQL. Gudu SQLFlow builds that map automatically by parsing T-SQL with a dedicated SQL Server procedural parser, so lineage does not stop at the object level the way SSMS dependency views do.

Why SQL Server data lineage is harder than it looks

On most SQL Server estates, the transformation logic that actually matters does not live in tidy views. It lives in stored procedures that stage data through #temp tables, upsert into fact tables with MERGE, branch on parameters, and assemble SQL strings at runtime with sp_executesql or EXEC. Any lineage approach that only reads object definitions from the catalog sees the procedure as a black box: it knows the proc exists, but not which columns flow through it.

Getting real lineage out of that code means parsing T-SQL the way the database engine does: building a semantic model of every statement in the procedure body, tracking columns as they pass through temp tables and table variables, and resolving the SQL hidden inside string variables. That is a compiler problem, not a metadata-query problem, and it is exactly what SQLFlow’s T-SQL parser is built for.

What SSMS dependency views can and cannot tell you

SQL Server ships useful dependency tooling, and it is the right first stop for quick object-level questions. sys.sql_expression_dependencies, sys.dm_sql_referenced_entities, and the “View Dependencies” dialog in SSMS reliably tell you that procedure A references table B. What they cannot tell you:

• Column-level flow. Catalog dependencies stop at the object level. They show that usp_load_fact_sales touches staging.sales and dw.fact_sales, but not that fact_sales.amount is derived from staging.sales.amount through a CAST while staging.sales.load_date only filters rows.
• Direction of flow. A reference is not a dataflow. Reading from a table and writing to it produce the same dependency row; lineage needs to know which is source and which is target.
• Dynamic SQL. Statements built in a string and run through sp_executesql or EXEC are invisible to the dependency catalog entirely, because they do not exist until runtime.
• Temp table hops. #temp tables live in tempdb and are not tracked as dependency endpoints, so any lineage that passes through one is cut in half.

Answering “what breaks downstream if I retype this column” or “which source fields feed this regulated report” requires parsing the T-SQL bodies themselves. That is the layer SQLFlow adds on top of what the catalog already gives you.

How SQLFlow traces lineage through T-SQL procedures

SQLFlow is built on the General SQL Parser (GSP), a commercial SQL compiler front-end developed since the mid-2000s and validated against roughly 13,600 per-dialect SQL test fixtures. Its SQL Server support is a dedicated T-SQL grammar, not a generic ANSI parser with exceptions bolted on, and it includes a full procedural parser for stored procedure bodies. Inside a procedure, SQLFlow resolves:

• Temp tables and table variables. Columns are tracked through SELECT ... INTO #staging, INSERT INTO @rows, and every subsequent read, so a three-hop pipeline through tempdb appears as one continuous lineage path.
• MERGE statements. Each WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT branch is analyzed, mapping source columns to target columns and treating the ON condition as indirect lineage.
• Dynamic SQL. SQL assembled into variables and executed via sp_executesql or EXEC is resolved and parsed rather than skipped — one of the most common blind spots in lineage tooling.
• Procedure parameters. Lineage flows through parameter values into the statements that use them.
• The call graph. When procedures call other procedures, SQLFlow renders an interactive procedure-to-procedure call graph, so you can see the whole ETL chain, not one proc at a time.

Worked example: staging to fact table via MERGE

Here is the kind of procedure every SQL Server warehouse contains — a load proc that cleans staging rows into a temp table, then merges them into a fact table:

CREATE PROCEDURE dbo.usp_load_fact_sales
AS
BEGIN
    SELECT s.order_id,
           s.customer_id,
           CAST(s.amount AS DECIMAL(18,2)) AS amount
    INTO   #clean_sales
    FROM   staging.sales s
    WHERE  s.load_date = CAST(GETDATE() AS DATE);

    MERGE dw.fact_sales AS tgt
    USING #clean_sales  AS src
       ON tgt.order_id = src.order_id
    WHEN MATCHED THEN
        UPDATE SET tgt.amount = src.amount
    WHEN NOT MATCHED THEN
        INSERT (order_id, customer_id, amount)
        VALUES (src.order_id, src.customer_id, src.amount);
END

Run this through SQLFlow and the diagram shows dw.fact_sales.amount fed by staging.sales.amount through a CAST, with #clean_sales visible as the intermediate hop. It also shows two relationships a catalog query can never produce: staging.sales.load_date as indirect lineage into every output column (it decides which rows load), and order_id doing double duty — direct lineage into the inserted key and indirect lineage through the MERGE ... ON match condition.

Direct vs indirect lineage in T-SQL

SQLFlow distinguishes direct lineage (a source column’s value lands in a target column) from indirect lineage (a column shapes the result through a WHERE clause, JOIN or MERGE ON condition, GROUP BY, or aggregate). The two are separately toggleable in the diagram. This matters in T-SQL work more than most dialects, because load procedures are full of filter columns — batch dates, status flags, watermark columns — that never appear in the output but silently control what data arrives. Most lineage tools do not model this distinction at all; for impact analysis on a load procedure, it is often the half that bites you.

Getting SQL Server lineage: inputs and deployment

You can feed SQL Server code to SQLFlow several ways, depending on how locked down your environment is:

Input	How it works	Good for
Paste or upload T-SQL	Scripts, DDL, and procedure definitions as files or pasted text	Quick analysis, code review, one procedure at a time
Live metadata over JDBC	SQLFlow pulls DDL, view and procedure definitions from the instance	Whole-database lineage that stays current
Grabit metadata extraction	Offline extraction utility ships metadata to SQLFlow	Environments where the lineage server cannot reach the database

In every case the analysis is static: SQLFlow reads SQL code and schema metadata, never the rows in your tables. For regulated environments, SQLFlow On-Premise runs on Docker or Kubernetes inside your network — air-gapped if required — so T-SQL source never leaves your infrastructure. Enterprise deployments batch-scan estates of 100+ databases and over a million columns with incremental scans, and export lineage to DataHub, Microsoft Purview, and OpenMetadata, so SQL Server lineage can flow into the catalog you already run. Pricing: the Cloud edition starts free ($49.99/month premium); On-Premise is $500/month or $4,800 one-time per database type — details on the pricing page.

What about open-source parsers and catalog platforms?

Open-source libraries like sqllineage and sqlglot parse individual queries well, and for plain SELECT and INSERT statements they may be all you need. T-SQL procedural code is where the gap opens: procedure bodies with control flow, #temp table state across statements, MERGE branch analysis, and dynamic SQL resolution are hard parser engineering that generic tools skip. Catalog-first platforms are strong at organizing metadata across many systems; for column lineage through stored procedure logic they typically need a parsing engine underneath — which is the role SQLFlow plays, standalone or as the lineage feed into those platforms. The honest test: take your longest production procedure and run it through each candidate.

Related lineage guides

• Azure SQL data lineage — the same T-SQL analysis applied to Azure SQL Database and Managed Instance.
• Oracle data lineage tool — PL/SQL procedural lineage, the Oracle counterpart to this page.
• SQL data lineage tool overview — how SQLFlow works across all 39 supported dialects.

Frequently asked questions

Can SQLFlow trace lineage through SQL Server dynamic SQL?

Yes. SQL assembled in string variables and executed with sp_executesql or EXEC is resolved and parsed inside the procedure analysis, so the tables and columns it touches appear in the lineage graph. Catalog-based dependency views cannot see dynamic SQL at all.

Does SQLFlow handle #temp tables and table variables?

Yes. Columns are tracked through #temp tables and table variables across statements, so a pipeline that stages data in tempdb shows up as one continuous column-level path from source table to final target.

How is this different from View Dependencies in SSMS?

SSMS dependency views report object-level references: procedure A mentions table B. SQLFlow parses the T-SQL bodies and reports column-level dataflow with direction — which source columns feed which target columns, through which transformations — including flows through temp tables, MERGE, and dynamic SQL that the catalog does not track.

Does SQLFlow need access to my SQL Server data?

No. SQLFlow performs static analysis of SQL code and optionally reads schema metadata such as table and procedure definitions. It never reads table row data, and the On-Premise edition keeps SQL text inside your network.

Can I see which stored procedures call which?

Yes. SQLFlow renders an interactive call graph of procedure-to-procedure invocations alongside the column lineage, so you can navigate a multi-procedure ETL chain end to end.

Is there a free way to try SQL Server lineage?

Yes. SQLFlow Cloud has a free tier: paste T-SQL in the browser and get a column-level lineage diagram. Premium is $49.99/month for larger inputs and API access.