SQL Server 2014: column dependencies / lineage

422 views Asked by At

I would like to know which columns of a table or view are part of a column in my current view.

For a "basic" version, I used columns for current view, I used sys.views and sys.dm_sql_referenced_entities ... and some other system catalog views.

CREATE TABLE Table1 
(
    ID INT IDENTITY(1,1) NOT NULL,
    Value1 INT
)

CREATE TABLE Table2 
(
    ID INT IDENTITY(1,1) NOT NULL,
    Value2 INT
)

INSERT INTO Table1 (Value1)
VALUES (1), (2), (77)

INSERT INTO Table2 (Value2)
VALUES (5), (7), (44)

CREATE VIEW View1_Sum_Value 
AS
    SELECT 
        Table1.ID, Table1.Value1 + Table2.Value2 AS Sum_Value
    FROM 
        Table1
    INNER JOIN 
        Table2 ON Table1.ID = Table2.ID

Result:

ID  | Sum_Value
----+----------
1   |   6
2   |   9
3   |   121

What I would like to get is something like this:

referencing_object  |   referenced_object   |   is select   |   type
--------------------+-----------------------+---------------+----------
View1.ID            |   Table1.ID           |   yes         |   SELECT
View1.Sum_Value     |   Table1.Value1       |   yes         |   SELECT
View2.Sum_Value     |   Table2.Value2       |   yes         |   SELECT
View2               |   Table2.ID           |   no          |   JOIN

Can somebody help me solve this problem?

0

There are 0 answers