Column Lineage in SQL

Question

Hello,

In my database, there is a table named "table1" with columns col1, col2, and col3. This table is referenced by multiple views:

So as per the above example the column "Col1" from table1 is being used directly or indirectly in below components

dbo.view1

dbo.view2

dbo.view3

xyz.view4

Now, I want to determine the column lineage specifically for "Col1" in "table1" within the database. Is it possible to retrieve this information using metadata tables or any other means?

@Nandan Hegde @Erland Sommarskog

Answer 1

You can use sp_describe_first_result_set (Transact-SQL) => "source_..." results

Answer 2

If we assume that one view could have

SELECT NewCol = col1 +1 FROM tbl

and the next view then refers to NewCol and so on, this starts to get hairy. I'm afraid that I have nothing canned for this. When I have encountered things like this, I have handled it manually. That is for every new hit, I've searched on the new references I've found. And the hits have not necessarily been restricted to SQL. "Hm, this piece client of client is reading this column. What is it doing with it?"

To do this on a larger scale - I would scan what's available from third-party tool providers.