reference.ftv_RepoObjectColumn_ReferenceTree - IF

type: IF ( inline function ), modify_date: 2021-09-24 22:39:45

RepoObject_guid: 3F4679B8-147C-EB11-84E6-A81E8446D5B0

Description

Examples

Parameters

  • @RepoObjectColumn_guid (uniqueidentifier)

  • @Referenced_Depth (int)

  • @Referencing_Depth (int)

Entity Diagram

entity-reference.ftv_repoobjectcolumn_referencetree

Columns

Table 1. Columns of reference.ftv_RepoObjectColumn_ReferenceTree - IF
PK Column Name Data Type NULL? ID

int

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

char(2)

NULL

nvarchar(392)

NULL

nvarchar(386)

NULL

nvarchar(128)

NULL

int

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

char(2)

NULL

nvarchar(392)

NULL

nvarchar(386)

NULL

nvarchar(128)

NULL

uniqueidentifier

NULL

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.ftv_repoobjectcolumn_referencetree

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.ftv_repoobjectcolumn_referencetree

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.ftv_repoobjectcolumn_referencetree

Column Reference Diagram

entity_1_1_colref-reference.ftv_repoobjectcolumn_referencetree

Column Details

_

Referenced_Depth

Referenced_Depth

int

NULL

Referenced_fullname

Referenced_fullname

nvarchar(261)

NULL

Referenced_fullname2

Referenced_fullname2

nvarchar(257)

NULL

Referenced_guid

Referenced_guid

uniqueidentifier

NULL

Referenced_RepoObject_guid

Referenced_RepoObject_guid

uniqueidentifier

NULL

Referenced_type

Referenced_type

char(2)

NULL

ReferencedColumn_fullname

ReferencedColumn_fullname

nvarchar(392)

NULL

ReferencedColumn_fullname2

ReferencedColumn_fullname2

nvarchar(386)

NULL

ReferencedColumn_name

ReferencedColumn_name

nvarchar(128)

NULL

Referencing_Depth

Referencing_Depth

int

NULL

Referencing_fullname

Referencing_fullname

nvarchar(261)

NULL

Referencing_fullname2

Referencing_fullname2

nvarchar(257)

NULL

Referencing_guid

Referencing_guid

uniqueidentifier

NULL

Referencing_RepoObject_guid

Referencing_RepoObject_guid

uniqueidentifier

NULL

Referencing_type

Referencing_type

char(2)

NULL

ReferencingColumn_fullname

ReferencingColumn_fullname

nvarchar(392)

NULL

ReferencingColumn_fullname2

ReferencingColumn_fullname2

nvarchar(386)

NULL

ReferencingColumn_name

ReferencingColumn_name

nvarchar(128)

NULL

RepoObjectColumn_guid

RepoObjectColumn_guid

uniqueidentifier

NULL

sql_modules_definition

reference.ftv_RepoObjectColumn_ReferenceTree - IF script
/*
--Duplicates are possible, if exists alternative path between objects with different depth
--to elimenate them, exclude Referenced_Depth and Referencing_Depth and use DISTINCT


DECLARE @RepoObjectColumn_guid uniqueidentifier

SET @RepoObjectColumn_guid = (SELECT RepoObjectColumn_guid from [repo].[RepoObjectColumn_gross] where RepoObjectColumn_fullname = '[repo].[RepoObjectColumn_gross].[RepoObjectColumn_guid]')

SELECT *
FROM [repo].[ftv_RepoObjectColumn_ReferenceTree](@RepoObjectColumn_guid, DEFAULT, DEFAULT)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [repo].[ftv_RepoObjectColumn_ReferenceTree](@RepoObjectColumn_guid, 1, 1)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [repo].[ftv_RepoObjectColumn_ReferenceTree](@RepoObjectColumn_guid, 0, 6)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [repo].[ftv_RepoObjectColumn_ReferenceTree](@RepoObjectColumn_guid, 100, 100)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]


*/
CREATE Function reference.ftv_RepoObjectColumn_ReferenceTree
(
    @RepoObjectColumn_guid UniqueIdentifier
  , @Referenced_Depth      Int = 0
  , @Referencing_Depth     Int = 0
)
Returns Table
As
Return
(
    --trees are recursive to get parent child relations
    With
    tree_referenced
    As
        (
        Select
            FirstNode.Referenced_fullname
          , FirstNode.Referenced_fullname2
          , FirstNode.Referenced_RepoObject_guid
          , FirstNode.Referenced_type
          , FirstNode.ReferencedColumn_fullname
          , FirstNode.ReferencedColumn_fullname2
          , FirstNode.ReferencedColumn_name
          , FirstNode.Referenced_guid
          , FirstNode.Referencing_fullname
          , FirstNode.Referencing_fullname2
          , FirstNode.Referencing_RepoObject_guid
          , FirstNode.Referencing_type
          , FirstNode.ReferencingColumn_fullname
          , FirstNode.ReferencingColumn_fullname2
          , FirstNode.ReferencingColumn_name
          , FirstNode.Referencing_guid
          , Referenced_Depth  = 1
          , Referencing_Depth = 0
        From
            reference.RepoObjectColumn_ReferencedReferencing As FirstNode
        Where
            FirstNode.Referencing_guid = @RepoObjectColumn_guid
            And 1                      <= @Referenced_Depth
        Union All
        Select
            child.Referenced_fullname
          , child.Referenced_fullname2
          , child.Referenced_RepoObject_guid
          , child.Referenced_type
          , child.ReferencedColumn_fullname
          , child.ReferencedColumn_fullname2
          , child.ReferencedColumn_name
          , child.Referenced_guid
          , child.Referencing_fullname
          , child.Referencing_fullname2
          , child.Referencing_RepoObject_guid
          , child.Referencing_type
          , child.ReferencingColumn_fullname
          , child.ReferencingColumn_fullname2
          , child.ReferencingColumn_name
          , child.Referencing_guid
          , Referenced_Depth = parent.Referenced_Depth + 1
          , 0
        From
            reference.RepoObjectColumn_ReferencedReferencing As child
            Inner Join
                tree_referenced                              As parent
                    On
                    child.Referencing_guid = parent.Referenced_guid
        Where
            parent.Referenced_Depth < @Referenced_Depth
        )
  ,
    tree_referencing
    As
        (
        Select
            FirstNode.Referenced_fullname
          , FirstNode.Referenced_fullname2
          , FirstNode.Referenced_RepoObject_guid
          , FirstNode.Referenced_type
          , FirstNode.ReferencedColumn_fullname
          , FirstNode.ReferencedColumn_fullname2
          , FirstNode.ReferencedColumn_name
          , FirstNode.Referenced_guid
          , FirstNode.Referencing_fullname
          , FirstNode.Referencing_fullname2
          , FirstNode.Referencing_RepoObject_guid
          , FirstNode.Referencing_type
          , FirstNode.ReferencingColumn_fullname
          , FirstNode.ReferencingColumn_fullname2
          , FirstNode.ReferencingColumn_name
          , FirstNode.Referencing_guid
          , Referenced_Depth  = 0
          , Referencing_Depth = 1
        From
            reference.RepoObjectColumn_ReferencedReferencing As FirstNode
        Where
            FirstNode.Referenced_guid = @RepoObjectColumn_guid
            And 1                     <= @Referencing_Depth
        Union All
        Select
            child.Referenced_fullname
          , child.Referenced_fullname2
          , child.Referenced_RepoObject_guid
          , child.Referenced_type
          , child.ReferencedColumn_fullname
          , child.ReferencedColumn_fullname2
          , child.ReferencedColumn_name
          , child.Referenced_guid
          , child.Referencing_fullname
          , child.Referencing_fullname2
          , child.Referencing_RepoObject_guid
          , child.Referencing_type
          , child.ReferencingColumn_fullname
          , child.ReferencingColumn_fullname2
          , child.ReferencingColumn_name
          , child.Referencing_guid
          , 0
          , Referencing_Depth = parent.Referencing_Depth + 1
        From
            reference.RepoObjectColumn_ReferencedReferencing As child
            Inner Join
                tree_referencing                             As parent
                    On
                    child.Referenced_guid = parent.Referencing_guid
        Where
            parent.Referencing_Depth < @Referencing_Depth
        )
    Select
        tree_referenced.Referenced_fullname
      , tree_referenced.Referenced_fullname2
      , tree_referenced.Referenced_RepoObject_guid
      , tree_referenced.Referenced_type
      , tree_referenced.ReferencedColumn_fullname
      , tree_referenced.ReferencedColumn_fullname2
      , tree_referenced.ReferencedColumn_name
      , tree_referenced.Referenced_guid
      , tree_referenced.Referencing_fullname
      , tree_referenced.Referencing_fullname2
      , tree_referenced.Referencing_RepoObject_guid
      , tree_referenced.Referencing_type
      , tree_referenced.ReferencingColumn_fullname
      , tree_referenced.ReferencingColumn_fullname2
      , tree_referenced.ReferencingColumn_name
      , tree_referenced.Referencing_guid
      , tree_referenced.Referenced_Depth
      , tree_referenced.Referencing_Depth
      , RepoObjectColumn_guid = @RepoObjectColumn_guid
    From
        tree_referenced
    Union
    Select
        tree_referencing.Referenced_fullname
      , tree_referencing.Referenced_fullname2
      , tree_referencing.Referenced_RepoObject_guid
      , tree_referencing.Referenced_type
      , tree_referencing.ReferencedColumn_fullname
      , tree_referencing.ReferencedColumn_fullname2
      , tree_referencing.ReferencedColumn_name
      , tree_referencing.Referenced_guid
      , tree_referencing.Referencing_fullname
      , tree_referencing.Referencing_fullname2
      , tree_referencing.Referencing_RepoObject_guid
      , tree_referencing.Referencing_type
      , tree_referencing.ReferencingColumn_fullname
      , tree_referencing.ReferencingColumn_fullname2
      , tree_referencing.ReferencingColumn_name
      , tree_referencing.Referencing_guid
      , tree_referencing.Referenced_Depth
      , tree_referencing.Referencing_Depth
      , RepoObjectColumn_guid = @RepoObjectColumn_guid
    From
        tree_referencing
);