Documenting ETL Procedure Data Flow from SQL Server Metadata
A nightly load runs three stored procedures in a row. Someone from the reporting team asks two questions: where do the rows in the sales fact table come from, and which step writes to the staging table? Nobody wrote the answers down, and the person who built the job has moved on. I wanted to know how much of that document SQL Server can produce from its own metadata, and which parts it gets wrong.
Two earlier posts cover nearby ground. Mapping Stored Procedure Dependencies with AI is about what breaks if you change an object. Understanding Unfamiliar Code: Reverse-Engineering Legacy Procedures is about reading a procedure you inherited. Here I only care about direction: which tables a procedure reads, which it writes, and how the data moves from one procedure to the next.
The Flags That Separate Reads from Writes
sys.sql_expression_dependencies tells you that a procedure refers to a table. It doesn’t say how. The function sys.dm_sql_referenced_entities returns one row for each object a module refers to by name, and four of its columns describe the kind of use. is_selected means the object or column is selected, and is_updated means it’s modified. is_select_all means it appears in a SELECT *, and is_insert_all means it’s the target of an INSERT with no column list.[1]
The documentation only shows an UPDATE example for “modified”. So I built a small ETL chain and checked every flag.
The Test Chain
I used two instances on one workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). Each got a scratch database named sat_etl_doc with four procedures in an etl schema, plus a second database, sat_etl_doc_remote, for the cross-database case:
p1_stage_orderstruncatesstg.orders, loads it fromsrc.orders, and writes a row toetl.run_log.p2_transform_ordersupdatesstg.ordersfromsrc.customers, copies orders with no matching customer tostg.rejects, and deletes them from staging.p3_load_dwmergessrc.customersintodw.dim_customerand inserts new rows fromstg.ordersintodw.fact_sales.p0_run_allcalls the other three.
I then added 13 small procedures, most of them testing one case from the list later in this post. The databases were at compatibility level 130 on SQL Server 2019 and 170 on SQL Server 2025. Every result below was the same on both versions, apart from the object IDs.
Mapping the Chain
This query starts at the runner, follows EXEC calls through sys.sql_expression_dependencies, and asks sys.dm_sql_referenced_entities about each procedure it finds. referenced_minor_id = 0 keeps the object-level rows and drops the per-column rows. OUTER APPLY keeps a procedure that returns no rows from the function. With @root set to etl.x_dynamic_sql, the query returned one row for it with an empty object and “no references found”. CROSS APPLY would drop that procedure from the map.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
USE [sat_etl_doc]; DECLARE @root nvarchar(256) = N'etl.p0_run_all'; WITH [chain] AS ( SELECT OBJECT_ID(@root) AS [proc_id] , 0 AS [depth] UNION ALL SELECT [d].[referenced_id] , [c].[depth] + 1 FROM [chain] AS [c] INNER JOIN [sys].[sql_expression_dependencies] AS [d] ON [d].[referencing_id] = [c].[proc_id] INNER JOIN [sys].[objects] AS [o] ON [o].[object_id] = [d].[referenced_id] WHERE [o].[type] = 'P' ) SELECT [c].[depth] , QUOTENAME(OBJECT_SCHEMA_NAME([c].[proc_id])) + N'.' + QUOTENAME(OBJECT_NAME([c].[proc_id])) AS [procedure] , CONCAT_WS(N'.', [re].[referenced_database_name], [re].[referenced_schema_name], [re].[referenced_entity_name]) AS [object] , CASE WHEN [re].[referenced_entity_name] IS NULL THEN N'no references found' WHEN [o].[type] = 'P' THEN N'calls' WHEN [re].[is_updated] = 1 AND ([re].[is_selected] = 1 OR [re].[is_select_all] = 1) THEN N'reads + writes' WHEN [re].[is_updated] = 1 THEN N'writes' WHEN [re].[is_selected] = 1 OR [re].[is_select_all] = 1 THEN N'reads' ELSE N'referenced, no flag set' END AS [access] FROM [chain] AS [c] OUTER APPLY [sys].[dm_sql_referenced_entities] ( OBJECT_SCHEMA_NAME([c].[proc_id]) + N'.' + OBJECT_NAME([c].[proc_id]) , N'OBJECT' ) AS [re] LEFT OUTER JOIN [sys].[objects] AS [o] ON [o].[object_id] = [re].[referenced_id] AND [re].[referenced_database_name] IS NULL WHERE COALESCE([re].[referenced_minor_id], 0) = 0 ORDER BY [c].[depth] , [procedure] , [access] , [object]; |
The output on both instances:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
depth procedure object access ----- -------------------------- ----------------------- -------------- 0 [etl].[p0_run_all] etl.p1_stage_orders calls 0 [etl].[p0_run_all] etl.p2_transform_orders calls 0 [etl].[p0_run_all] etl.p3_load_dw calls 1 [etl].[p1_stage_orders] src.orders reads 1 [etl].[p1_stage_orders] etl.run_log writes 1 [etl].[p1_stage_orders] stg.orders writes 1 [etl].[p2_transform_orders] src.customers reads 1 [etl].[p2_transform_orders] stg.orders reads + writes 1 [etl].[p2_transform_orders] stg.rejects writes 1 [etl].[p3_load_dw] src.customers reads 1 [etl].[p3_load_dw] stg.orders reads 1 [etl].[p3_load_dw] dw.fact_sales reads + writes 1 [etl].[p3_load_dw] dw.dim_customer writes |
That matches the code. In these four procedures, is_updated was 1 for the target of every INSERT, UPDATE, DELETE and MERGE, so one flag covers all four statements. dw.fact_sales shows as read and written because p3_load_dw checks it with NOT EXISTS before inserting. The MERGE target dw.dim_customer showed is_selected = 0, even though the ON clause reads it.
Grouping the same rows by table shows the handoffs between steps. I called the same function for each of the four procedures and used STRING_AGG to list the writers and readers of each table:
|
1 2 3 4 5 6 7 8 9 |
object written_by read_by --------------- ------------------------------------- ------------------------------ dw.dim_customer p3_load_dw NULL dw.fact_sales p3_load_dw p3_load_dw etl.run_log p1_stage_orders NULL src.customers NULL p2_transform_orders, p3_load_dw src.orders NULL p1_stage_orders stg.orders p1_stage_orders, p2_transform_orders p2_transform_orders, p3_load_dw stg.rejects p2_transform_orders NULL |
In this chain, src.orders and src.customers are the sources, and the outputs are the four tables no other procedure reads (I don’t count p3_load_dw reading its own target, dw.fact_sales). stg.orders is the handoff from staging to transform to load.
The flags are per procedure. When I paired every table each procedure reads with every table it writes, p3_load_dw produced five source-to-target pairs. Only two of them match a statement in the code: src.customers to dw.dim_customer, and stg.orders to dw.fact_sales. For column-to-column lineage you still need to read the procedure.
Where the Metadata Falls Short
The next table lists what sys.dm_sql_referenced_entities reported for each of the extra procedures.
| Case | What the function reported |
|---|---|
INSERT with a column list |
Target is_updated = 1, is_insert_all = 0 |
INSERT with no column list |
Target is_updated = 1, is_insert_all = 1, and no column rows |
SELECT * only |
is_selected = 0, is_select_all = 1 |
TRUNCATE TABLE only |
A row for the table with every flag 0 |
SELECT ... INTO a new permanent table |
No row for the new table, even after the procedure ran and the table existed |
Dynamic SQL (sp_executesql and EXEC (...)) |
No rows at all |
CREATE TABLE #work, then UPDATE ... FROM a join to it |
Every flag 0 on every table, plus a row for the alias f as if it were a table |
SELECT ... INTO #work, then INSERT from #work |
Source is_selected = 1, but target is_updated = 0 |
| Synonyms | The synonym names, not the tables, and is_insert_all = 1 despite a column list |
| Table that doesn’t exist yet | referenced_id NULL and is_incomplete = 1, and the real source table in the same statement lost is_selected |
Same table after CREATE TABLE |
Resolved, with correct flags, without altering the procedure |
| Three-part name in another database | Correct flags, with an object ID from the other database |
Filter on is_select_all as well. A procedure that reads a table only through SELECT * gets is_selected = 0. A filter on is_selected = 1 alone drops that read.
TRUNCATE and SELECT INTO. Both are common in staging steps. A procedure that only truncates a table looks like it refers to the table without using it. A procedure that creates a table with SELECT ... INTO has no row for that table at all.
Temp tables. The documentation says dependency information isn’t kept for temporary tables.[2] In my two tests the permanent tables were affected too, in different ways. x_temp_table creates #work with CREATE TABLE, loads it from src.orders, and then updates dw.fact_sales through a join to it. Every permanent table in that procedure lost its flags. x_temp_table_into builds #work with SELECT ... INTO and then inserts from it into dw.fact_sales. There, src.orders kept is_selected = 1, and only dw.fact_sales lost is_updated when its source was #work. A procedure that stages through temp tables can show missing reads, missing writes, or both. sys.sql_expression_dependencies also listed the alias f from x_temp_table as a referenced entity with a NULL ID.
Deferred name resolution. SQL Server lets you create a procedure that refers to a table that doesn’t exist yet.[3] In an ETL database this can happen when a later step or a deployment script creates the table. x_deferred_missing inserts into a table I never created. The function returned that table with a NULL referenced_id and is_incomplete = 1.
Outside a TRY block, querying the function for x_deferred_missing raised Msg 2020. A CROSS APPLY over every procedure in the database returned the six procedures that sort before x_deferred_missing and then stopped with that error. Inside a TRY block the error never appeared, even with one CROSS APPLY over every procedure. A plain SELECT for x_deferred_missing returned its two rows, and the CROSS APPLY returned all 16 procedures that have references. Both times @@ERROR was 0 and the CATCH block didn’t run. So I don’t rely on the error. I treat is_incomplete = 1 as “check this one by hand”.
x_deferred refers to two tables that didn’t exist when I created it, src.returns and dw.fact_returns. Once I created them, both the function and the catalog view resolved them, with the right flags, without an ALTER or sp_refreshsqlmodule.
Cross-database references. sys.sql_expression_dependencies returns a NULL referenced_id for these, as documented.[2] sys.dm_sql_referenced_entities returned the right flags and an ID, but that ID belongs to the other database. Both of my databases were new, and their first tables got the same object IDs. OBJECT_NAME() run in the local database turned the IDs for remote_src and remote_tgt into orders and customers, two unrelated local tables. In real databases the ID may point to an unrelated table or to nothing. Pass the database ID to OBJECT_NAME(), or skip the join when referenced_database_name is not NULL. What Reaches Outside Your Database? sys.dm_db_uncontained_entities covers another way to find these references.
Synonyms. is_selected and is_updated were right, but the names were the synonyms. sys.synonyms has the target in base_object_name, so one more join turns the synonym into the table it points to.[4]
Finding Procedures to Check by Hand
For the cases above, I search the module text for patterns, and I check sys.sql_expression_dependencies for three-part names and synonyms. The text match is crude. It will flag a # inside a comment, and it labelled x_temp_table_into only as “temp table” because that procedure also has an INSERT INTO. The EXEC%(% pattern matches any procedure with EXEC somewhere before an opening parenthesis. That includes EXECUTE AS, or a plain EXEC followed later by COUNT(*), so in a real database it flags many ordinary procedures. I use the result as a reading list.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
USE [sat_etl_doc]; SELECT [f].[procedure] , [f].[check_by_hand] FROM ( SELECT QUOTENAME(OBJECT_SCHEMA_NAME([m].[object_id])) + N'.' + QUOTENAME(OBJECT_NAME([m].[object_id])) AS [procedure] , CONCAT_WS ( N', ' , CASE WHEN [m].[definition] LIKE N'%sp[_]executesql%' OR [m].[definition] LIKE N'%EXEC%(%' THEN N'dynamic SQL' END , CASE WHEN [m].[definition] LIKE N'%#%' THEN N'temp table' END , CASE WHEN [m].[definition] LIKE N'%TRUNCATE TABLE%' THEN N'TRUNCATE' END , CASE WHEN [m].[definition] LIKE N'%INTO%FROM%' AND [m].[definition] NOT LIKE N'%INSERT%INTO%' THEN N'SELECT INTO' END , CASE WHEN EXISTS ( SELECT 1 FROM [sys].[sql_expression_dependencies] AS [d] WHERE [d].[referencing_id] = [m].[object_id] AND [d].[referenced_database_name] IS NOT NULL ) THEN N'cross-database' END , CASE WHEN EXISTS ( SELECT 1 FROM [sys].[sql_expression_dependencies] AS [d] INNER JOIN [sys].[synonyms] AS [s] ON [s].[object_id] = [d].[referenced_id] WHERE [d].[referencing_id] = [m].[object_id] ) THEN N'synonym' END ) AS [check_by_hand] FROM [sys].[sql_modules] AS [m] INNER JOIN [sys].[procedures] AS [p] ON [p].[object_id] = [m].[object_id] ) AS [f] WHERE [f].[check_by_hand] <> N'' ORDER BY [f].[procedure]; |
In the test database it returned eight procedures, including p1_stage_orders for its TRUNCATE, x_cross_db and x_synonym. Checking for a table row with every flag 0 or is_incomplete = 1 added one more, x_deferred_missing. I left out the rows for procedure calls, because the calls from p0_run_all also have every flag 0. That gave me nine procedures to read by hand, and eight I could document from metadata.
Limits of This Test
- I tested one build of each version, on one workstation, with small procedures written for the test.
- I didn’t test linked servers, CLR procedures, triggers on the target tables, or procedures called through a variable name.
- Real ETL code mixes these cases inside one procedure. In my tests a temp table or a missing table could clear the flags on permanent tables in the same statement. It didn’t always:
src.orderskeptis_selectedin theSELECT ... INTO #work. - I tested compatibility level 130 on SQL Server 2019 and 170 on SQL Server 2025, and no other levels.
The metadata gave a correct read and write map for procedures that read and write permanent tables by two-part name. For temp tables, TRUNCATE, SELECT INTO, synonyms and cross-database IDs, it returned missing or misleading rows. The missing table raised an error only outside a TRY block. I’d use the metadata as the first draft of the document and check the flagged procedures by hand.
Related: Trigger Archaeology: Mapping Nested and Recursive Trigger Chains.
If you’ve built data-flow documentation from SQL Server metadata, or found other cases where these flags are wrong, I’d like to hear about it. Find me on Bluesky or LinkedIn.
References
- sys.dm_sql_referenced_entities (Transact-SQL) - Microsoft Learn. Defines is_selected, is_updated, is_select_all, is_insert_all and is_incomplete, and documents error 2020 when column dependencies can’t be resolved. ↩
- sys.sql_expression_dependencies (Transact-SQL) - Microsoft Learn. States that dependency information isn’t kept for temporary tables, and that referenced_id is always NULL for cross-database references. ↩
- CREATE TRIGGER (Transact-SQL), Deferred name resolution - Microsoft Learn. States that stored procedures, triggers, functions and batches can refer to tables that don’t exist at compile time. ↩
- sys.synonyms (Transact-SQL) - Microsoft Learn. Documents base_object_name, the fully quoted name of the object a synonym points to. ↩