如何在视图中获取列级依赖项 [英] How to get column-level dependencies in a view

问题描述

我已经对此事进行了一些研究，但还没有解决方案.我想得到的是视图中的列级依赖项.所以，假设我们有一张这样的表

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
)

和这样的视图:

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

我试过 sys.dm_sql_referenced_entities 函数，但 referencing_minor_id 对于视图始终为 0.

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

同样适用于 sys.sql_expression_dependencies 和过时的 sys.sql_dependencies.

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:我尝试使用 DAC 来查询此信息是否存储在 系统基表 但没找到

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

推荐答案

此解决方案只能部分回答您的问题.它不适用于表达式列.

This solution could answer your question only partially. It won't work for columns that are expressions.

您可以使用 sys.dm_exec_describe_first_result_set 获取列信息:

You could use sys.dm_exec_describe_first_result_set to get column information:

@include_browse_information

如果设置为 1，则每个查询都会被分析，就好像它在查询上具有 FOR BROWSE 选项一样.返回额外的键列和源表信息.

If set to 1, each query is analyzed as if it has a FOR BROWSE option on the query. Additional key columns and source table information are returned.

CREATE TABLE txu(id INT, first_name VARCHAR(10), last_name VARCHAR(10));
CREATE TABLE txd(id INT, id_fk INT, address VARCHAR(100));

CREATE VIEW v_txu
AS
SELECT t.id AS PK_id,
       t.first_name  AS name,
       d.address,
       t.first_name + t.last_name AS name_full
FROM txu t
JOIN txd d
  ON t.id = d.id_fk

主要查询:

SELECT name, source_database, source_schema,
      source_table, source_column 
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM v_txu', null, 1) ;  

输出:

+-----------+--------------------+---------------+--------------+---------------+
|   name    |   source_database  | source_schema | source_table | source_column |
+-----------+--------------------+---------------+--------------+---------------+
| PK_id     | fiddle_0f9d47226c4 | dbo           | txu          | id            |
| name      | fiddle_0f9d47226c4 | dbo           | txu          | first_name    |
| address   | fiddle_0f9d47226c4 | dbo           | txd          | address       |
| name_full | null               | null          | null         | null          |
+-----------+--------------------+---------------+--------------+---------------+

DBFiddleDemo

这篇关于如何在视图中获取列级依赖项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！