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?