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