How to get column-level dependencies in a view

Viewed 2857

I've made some research on the matter but don't have solution yet. What I want to get is column-level dependencies in a view. So, let's say we have a table like this

create table TEST(
    first_name varchar(10),
    last_name varchar(10),
    street varchar(10),
    number int
)

and a view like this:

create view vTEST
as
    select
        first_name + ' ' + last_name as [name],
        street + ' ' + cast(number as varchar(max)) as [address]
    from dbo.TEST

What I'd like is to get result like this:

column_name depends_on_column_name depends_on_table_name
----------- --------------------- --------------------
name        first_name            dbo.TEST
name        last_name             dbo.TEST
address     street                dbo.TEST
address     number                dbo.TEST

I've tried sys.dm_sql_referenced_entities function, but referencing_minor_id is always 0 there for views.

select
    referencing_minor_id,
    referenced_schema_name + '.' + referenced_entity_name as depends_on_table_name,
    referenced_minor_name as depends_on_column_name
from sys.dm_sql_referenced_entities('dbo.vTEST', 'OBJECT')

referencing_minor_id depends_on_table_name depends_on_column_name
-------------------- --------------------- ----------------------
0                    dbo.TEST              NULL
0                    dbo.TEST              first_name
0                    dbo.TEST              last_name
0                    dbo.TEST              street
0                    dbo.TEST              number

The same is true for sys.sql_expression_dependencies and for obsolete sys.sql_dependencies.

So do I miss something or is it impossible to do?

There're some related questions (Find the real column name of an alias used in a view?), but as I said - I haven't found a working solution yet.

EDIT 1: I've tried to use DAC to query if this information is stored somewhere in System Base Tables but haven't find it

5 Answers
Related