1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Falko
  • 206
  • 1
  • 8
  • What kind of advice are you hoping to get? To do proper detailed report like this even for complex queries, you'll probably have to perform full parsing of the SQL code - expect several months to years of effort to do this (my own experience - I'm developer at https://getmanta.com that provides tool to do exactly this kind of analysis). You might want to consider using a third-party tool depending on your situation. – Jiri Tousek Feb 22 '18 at 10:35
  • Thanks for your reply. I was in contact with Manta last year (it's a great tool, but a bit expensvie). – Falko Mar 02 '18 at 06:35

0 Answers0