t-sql 이란




보기에서 열 수준 종속성을 얻는 방법 (4)

나는이 문제에 대해 조사를했지만 해결책은 아직 없다. 내가 얻고 자하는 것은 뷰에서 컬럼 레벨 종속성입니다. 그래서 우리가 이런 테이블을 가지고 있다고 가정 해 봅시다.

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

내가 원하는 것은 다음과 같은 결과를 얻는 것입니다.

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입니다.

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 대해서도 마찬가지입니다.

그래서 나는 무언가를 놓치거나 할 수 없는가?

거기에 몇 가지 관련 질문 ( 보기에서 사용되는 별칭의 실제 열 이름을 찾으십시오? ),하지만 내가 말했듯이 - 나는 아직 일하는 해결책을 찾지 못했습니다.

편집 1 :이 정보가 시스템 기본 테이블 어딘가에 저장되어 있지만 그것을 찾지 못했다면 쿼리 DAC를 사용해 보았습니다.


나는 이것으로 놀고 있었지만 더 이상 갈 시간이 없었다. 아마도 이것이 도움이 될 것입니다 :

-- Returns all table columns called in the view and the objects they pull from

SELECT
     v.[name] AS ViewName
    ,d.[referencing_id] AS ViewObjectID 
    ,c.[name] AS ColumnNames
    ,OBJECT_NAME(d.referenced_id) AS ReferencedTableName
    ,d.referenced_id AS TableObjectIDsReferenced
FROM 
sys.views v 
INNER JOIN sys.sql_expression_dependencies d ON d.referencing_id = v.[object_id]
INNER JOIN sys.objects o ON d.referencing_id = o.[object_id]
INNER JOIN sys.columns c ON d.referenced_id = c.[object_id]
WHERE v.[name] = 'vTEST'

-- Returns all output columns in the view

SELECT 
     OBJECT_NAME([object_id]) AS ViewName
    ,[object_id] AS ViewObjectID
    ,[name] AS OutputColumnName
FROM sys.columns
WHERE OBJECT_ID('vTEST') = [object_id]

-- Get the view definition

SELECT 
    VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'vTEST'

당신이 필요로하는 모든 것은보기의 정의에 언급됩니다.

그래서 우리는 다음 단계를 따라이 정보를 추출 할 수 있습니다 :

  1. 뷰 정의를 문자열 변수에 지정하십시오.

  2. 콤마 (,)로 분리하십시오.

  3. XML로 CROSS APPLY를 사용하여 (+) 더하기 연산자로 별칭을 분할하십시오.

  4. 원본 테이블과 같은 정확한 정보를 얻으려면 시스템 테이블을 사용하십시오.

데모:-

Create PROC psp_GetLevelDependsView (@sViewName varchar(200))
AS
BEGIN

    Declare @stringToSplit nvarchar(1000),
            @name NVARCHAR(255),
            @dependsTableName NVARCHAR(50),
            @pos INT

    Declare @returnList TABLE ([Name] [nvarchar] (500))

    SELECT TOP 1 @dependsTableName= table_schema + '.'+  TABLE_NAME
    FROM    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

    select @stringToSplit = definition
    from sys.objects     o
    join sys.sql_modules m on m.object_id = o.object_id
    where o.object_id = object_id( @sViewName)
     and o.type = 'V'

     WHILE CHARINDEX(',', @stringToSplit) > 0
     BEGIN
        SELECT @pos  = CHARINDEX(',', @stringToSplit)  
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

        INSERT INTO @returnList 
        SELECT @name

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)[email protected])
     END

     INSERT INTO @returnList
     SELECT @stringToSplit

    select COLUMN_NAME  ,  b.Name as Expression
    Into #Temp
    FROM INFORMATION_SCHEMA.COLUMNS a , @returnList b
    WHERE TABLE_NAME= @sViewName
    And (b.Name) like '%' + ( COLUMN_NAME) + '%'

    SELECT A.COLUMN_NAME as column_name,  
         Split.a.value('.', 'VARCHAR(100)') AS depends_on_column_name ,   @dependsTableName as depends_on_table_name
         Into #temp2
     FROM  
     (
         SELECT COLUMN_NAME,  
             CAST ('<M>' + REPLACE(Expression, '+', '</M><M>') + '</M>' AS XML) AS Data  
         FROM  #Temp
     ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

    SELECT b.column_name , a.COLUMN_NAME as depends_on_column_name , b.depends_on_table_name
    FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a , #temp2 b
    WHERE VIEW_NAME= @sViewName
    and b.depends_on_column_name  like '%' + a.COLUMN_NAME + '%'

     drop table #Temp
     drop table #Temp2

 END

테스트:-

exec psp_GetLevelDependsView 'vTest'

결과:-

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_exec_describe_first_result_set 을 사용하여 열 정보를 얻을 수 있습니다.

@include_browse_information

1로 설정하면 각 조회는 조회에 FOR BROWSE 옵션이있는 것처럼 분석됩니다. 추가 키 컬럼과 소스 테이블 정보가 리턴됩니다.

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


쿼리 계획을 기반으로 한 솔루션입니다. 그것은 일부 모험을한다.

  • 거의 모든 선택 쿼리를 처리 할 수 ​​있습니다.
  • SchemaBinding 없음

불리한 점

  • 아직 제대로 테스트되지 않았다.
  • Microsoft가 XML 쿼리 계획을 변경하면 갑자기 부서 질 수 있습니다.

핵심 개념은 XML 쿼리 계획 내의 모든 열 표현식이 "DefinedValue"노드에 정의된다는 것입니다. "DefinedValue"의 첫 번째 하위 노드는 출력 열에 대한 참조이고 두 번째 노드는 표현식입니다. 이 표현식은 입력 열과 상수 값을 계산합니다. 위에서 언급했듯이 이것은 경험적 관찰에만 근거하고 있으므로 제대로 테스트해야합니다.

이것은 호출의 예입니다.

exec dbo.GetColumnDependencies 'select * from dbo.vTEST'

target_column_name | source_column_name        | const_value
---------------------------------------------------
address            | Expr1007                  | NULL
name               | Expr1006                  | NULL
Expr1006           | NULL                      | ' '
Expr1006           | [testdb].[dbo].first_name | NULL
Expr1006           | [testdb].[dbo].last_name  | NULL
Expr1007           | NULL                      | ' '
Expr1007           | [testdb].[dbo].number     | NULL
Expr1007           | [testdb].[dbo].street     | NULL

그것은 코드입니다. 우선 XML 쿼리 계획을 얻습니다.

declare @select_query as varchar(4000) = 'select * from dbo.vTEST' -- IT'S YOUR QUERY HERE.
declare @select_into_query    as varchar(4000) = 'select top (1) * into #foo from (' + @select_query + ') as src'
      , @xml_plan             as xml           = null
      , @xml_generation_tries as tinyint       = 10
;
while (@xml_plan is null and @xml_generation_tries > 0) -- There is no guaranty that plan will be cached.
begin 
  execute (@select_into_query);
  select @xml_plan = pln.query_plan
    from sys.dm_exec_query_stats as qry
      cross apply sys.dm_exec_sql_text(qry.sql_handle) as txt
      cross apply sys.dm_exec_query_plan(qry.plan_handle) as pln
    where txt.text = @select_into_query
  ;
end
if (@xml_plan is null
) begin
    raiserror(N'Can''t extract XML query plan from cache.' ,15 ,0);
    return;
  end
;

다음은 주요 쿼리입니다. 가장 중요한 부분은 열 추출을위한 재귀 공통 테이블 식입니다.

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
                  ,'http://schemas.microsoft.com/sqlserver/2004/07/showplan' as shp -- Used in .query() for predictive namespace using. 
)
    , cte_column_dependencies as
    (

재귀의 시드는 관심있는 선택 쿼리의 1 행을 저장하는 #foo 테이블의 열을 추출하는 쿼리입니다.

select
    (select foo_col.info.query('./ColumnReference') for xml raw('shp:root') ,type) -- Becouse .value() can't extract attribute from root node.
      as target_column_info
  , (select foo_col.info.query('./ScalarOperator/Identifier/ColumnReference') for xml raw('shp:root') ,type)
      as source_column_info
  , cast(null as xml) as const_info
  , 1 as iteration_no
from @xml_plan.nodes('//Update/SetPredicate/ScalarOperator/ScalarExpressionList/ScalarOperator/MultipleAssign/Assign')
        as foo_col(info)
where foo_col.info.exist('./ColumnReference[@Table="[#foo]"]') = 1

재귀 부분은 종속 열이있는 "DefinedValue"노드를 검색하고 열 표현식에 사용 된 모든 "ColumnReference"및 "Const"하위 노드를 추출합니다. XML에서 SQL 로의 변환이 복잡해집니다.

union all    
select
    (select internal_col.info.query('.') for xml raw('shp:root') ,type)
  , source_info.column_info
  , source_info.const_info
  , prev_dependencies.iteration_no + 1
from @xml_plan.nodes('//DefinedValue/ColumnReference') as internal_col(info)
  inner join cte_column_dependencies as prev_dependencies -- Filters by depended columns.
        on prev_dependencies.source_column_info.value('(//ColumnReference/@Column)[1]' ,'nvarchar(4000)') = internal_col.info.value('(./@Column)[1]' ,'nvarchar(4000)')
        and exists (select prev_dependencies.source_column_info.value('(.//@Schema)[1]'   ,'nvarchar(4000)') intersect select internal_col.info.value('(./@Schema)[1]'   ,'nvarchar(4000)'))
        and exists (select prev_dependencies.source_column_info.value('(.//@Database)[1]' ,'nvarchar(4000)') intersect select internal_col.info.value('(./@Database)[1]' ,'nvarchar(4000)'))
        and exists (select prev_dependencies.source_column_info.value('(.//@Server)[1]'   ,'nvarchar(4000)') intersect select internal_col.info.value('(./@Server)[1]'   ,'nvarchar(4000)'))
  cross apply ( -- Becouse only column or only constant can be places in result row.
            select (select source_col.info.query('.') for xml raw('shp:root') ,type) as column_info
                 , null                                                              as const_info
              from internal_col.info.nodes('..//ColumnReference') as source_col(info)
            union all
            select null                                                         as column_info
                 , (select const.info.query('.') for xml raw('shp:root') ,type) as const_info
              from internal_col.info.nodes('..//Const') as const(info)
        ) as source_info
where source_info.column_info is null
    or (
        -- Except same node selected by '..//ColumnReference' from its sources. Sorry, I'm not so well to check it with XQuery simple.
            source_info.column_info.value('(//@Column)[1]' ,'nvarchar(4000)') <> internal_col.info.value('(./@Column)[1]' ,'nvarchar(4000)')
        and (select source_info.column_info.value('(//@Schema)[1]'   ,'nvarchar(4000)') intersect select internal_col.info.value('(./@Schema)[1]'   ,'nvarchar(4000)')) is null
        and (select source_info.column_info.value('(//@Database)[1]' ,'nvarchar(4000)') intersect select internal_col.info.value('(./@Database)[1]' ,'nvarchar(4000)')) is null
        and (select source_info.column_info.value('(//@Server)[1]'   ,'nvarchar(4000)') intersect select internal_col.info.value('(./@Server)[1]'   ,'nvarchar(4000)')) is null
      )
)

마지막으로 XML을 적절한 사람 텍스트로 변환하는 Select 문입니다.

select
  --  col_dep.target_column_info
  --, col_dep.source_column_info
  --, col_dep.const_info
    coalesce(col_dep.target_column_info.value('(.//shp:ColumnReference/@Server)[1]'   ,'nvarchar(4000)') + '.' ,'')
  + coalesce(col_dep.target_column_info.value('(.//shp:ColumnReference/@Database)[1]' ,'nvarchar(4000)') + '.' ,'')
  + coalesce(col_dep.target_column_info.value('(.//shp:ColumnReference/@Schema)[1]'   ,'nvarchar(4000)') + '.' ,'')
  + col_dep.target_column_info.value('(.//shp:ColumnReference/@Column)[1]' ,'nvarchar(4000)')
    as target_column_name
  , coalesce(col_dep.source_column_info.value('(.//shp:ColumnReference/@Server)[1]'   ,'nvarchar(4000)') + '.' ,'')
  + coalesce(col_dep.source_column_info.value('(.//shp:ColumnReference/@Database)[1]' ,'nvarchar(4000)') + '.' ,'')
  + coalesce(col_dep.source_column_info.value('(.//shp:ColumnReference/@Schema)[1]'   ,'nvarchar(4000)') + '.' ,'')
  + col_dep.source_column_info.value('(.//shp:ColumnReference/@Column)[1]' ,'nvarchar(4000)')
    as source_column_name
  , col_dep.const_info.value('(/shp:root/shp:Const/@ConstValue)[1]' ,'nvarchar(4000)')
    as const_value
from cte_column_dependencies as col_dep
order by col_dep.iteration_no ,target_column_name ,source_column_name
option (maxrecursion 512) -- It's an assurance from infinite loop.




sql-server-2016