reference.ftv_RepoObject_ReferenceTree_referencing - IF

type: IF ( inline function ), modify_date: 2021-10-02 13:23:34

RepoObject_guid: 64CDA946-459D-EB11-84F6-A81E8446D5B0

Description

Examples

Example 1. Usage
DECLARE @RepoObject_guid uniqueidentifier

SET @RepoObject_guid = (SELECT RepoObject_guid from [repo].[RepoObject] where RepoObject_fullname = '[repo].[RepoObject_gross]')

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, DEFAULT, DEFAULT)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, 1, 1)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, 0, 6)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, 100, 100)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

Parameters

  • @RepoObject_guid (uniqueidentifier)

  • @Referenced_Depth (int)

  • @Referencing_Depth (int)

Entity Diagram

entity-reference.ftv_repoobject_referencetree_referencing

Columns

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

int

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

int

NULL

char(2)

NULL

int

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

int

NULL

varchar(2)

NULL

uniqueidentifier

NULL

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.ftv_repoobject_referencetree_referencing

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.ftv_repoobject_referencetree_referencing

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.ftv_repoobject_referencetree_referencing

Column Reference Diagram

entity_1_1_colref-reference.ftv_repoobject_referencetree_referencing

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_is_DocsOutput

referenced_is_DocsOutput

int

NULL

Referenced_type

Referenced_type

char(2)

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_is_DocsOutput

referencing_is_DocsOutput

int

NULL

Referencing_type

Referencing_type

varchar(2)

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NULL

sql_modules_definition

reference.ftv_RepoObject_ReferenceTree_referencing - 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


<<property_start>>exampleUsage
DECLARE @RepoObject_guid uniqueidentifier

SET @RepoObject_guid = (SELECT RepoObject_guid from [repo].[RepoObject] where RepoObject_fullname = '[repo].[RepoObject_gross]')

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, DEFAULT, DEFAULT)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, 1, 1)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, 0, 6)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree_referencing](@RepoObject_guid, 100, 100)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]
<<property_end>>


*/
CREATE Function reference.ftv_RepoObject_ReferenceTree_referencing
(
    @RepoObject_guid   UniqueIdentifier
  , @Referenced_Depth  Int = 0 --has no effect
  , @Referencing_Depth Int = 0
)
Returns Table
As
Return
(
    --trees are recursive to get parent child relations
    With
    --tree_referenced
    --As
    --    (
    --    Select
    --        FirstNode.*
    --      , 1 As Referenced_Depth
    --      , 0 As Referencing_Depth
    --    From
    --        graph.RepoObject_ReferencingReferenced As FirstNode
    --    --INNER JOIN [config].[type] t1
    --    -- ON t1.[type] = FirstNode.Referenced_type
    --    --INNER JOIN [config].[type] t2
    --    -- ON t2.[type] = FirstNode.Referencing_type
    --    Where
    --        Referencing_guid = @RepoObject_guid
    --        And 1            <= @Referenced_Depth
    --    --AND t1.[is_DocsOutput] = 1
    --    --AND t2.[is_DocsOutput] = 1
    --    Union All
    --    Select
    --        child.*
    --      , Referenced_Depth = parent.Referenced_Depth + 1
    --      , 0
    --    From
    --        graph.RepoObject_ReferencingReferenced As child
    --        Inner Join
    --            tree_referenced                    As parent
    --                On
    --                child.Referencing_guid = parent.Referenced_guid
    --    --INNER JOIN [config].[type] t1
    --    -- ON t1.[type] = child.Referenced_type
    --    ----INNER JOIN [config].[type] t2
    --    ---- ON t2.[type] = child.Referencing_type
    --    Where
    --        parent.Referenced_Depth < @Referenced_Depth
    --    --AND t1.[is_DocsOutput] = 1
    --    ----AND t2.[is_DocsOutput] = 1
    --    )
    --,
    tree_referencing
    As
        (
        Select
            FirstNode.referenced_fullname
          , FirstNode.referenced_fullname2
          , FirstNode.Referenced_guid
          , FirstNode.referenced_is_DocsOutput
          , FirstNode.referenced_type
          , FirstNode.referencing_fullname
          , FirstNode.referencing_fullname2
          , FirstNode.Referencing_guid
          , FirstNode.referencing_type
          , FirstNode.referencing_is_DocsOutput
          , Referenced_Depth  = 0
          , Referencing_Depth = 1
        From
            reference.RepoObject_ReferencedReferencing As FirstNode
        --INNER JOIN [config].[type] t1
        -- ON t1.[type] = FirstNode.Referenced_type
        --INNER JOIN [config].[type] t2
        -- ON t2.[type] = FirstNode.Referencing_type
        Where
            FirstNode.Referenced_guid = @RepoObject_guid
            And 1                     <= @Referencing_Depth
        --AND t1.[is_DocsOutput] = 1
        --AND t2.[is_DocsOutput] = 1
        Union All
        Select
            child.referenced_fullname
          , child.referenced_fullname2
          , child.Referenced_guid
          , child.referenced_is_DocsOutput
          , child.referenced_type
          , child.referencing_fullname
          , child.referencing_fullname2
          , child.Referencing_guid
          , child.referencing_type
          , child.referencing_is_DocsOutput
          , 0
          , Referencing_Depth = parent.Referencing_Depth + 1
        From
            reference.RepoObject_ReferencedReferencing As child
            Inner Join
                tree_referencing                       As parent
                    On
                    child.Referenced_guid = parent.Referencing_guid
        ----INNER JOIN [config].[type] t1
        ---- ON t1.[type] = child.Referenced_type
        --INNER JOIN [config].[type] t2
        -- ON t2.[type] = child.Referencing_type
        Where
            parent.Referencing_Depth < @Referencing_Depth
        ----AND t1.[is_DocsOutput] = 1
        --AND t2.[is_DocsOutput] = 1
        )
    --Select
    --    *
    --  , @RepoObject_guid As RepoObject_guid
    --From
    --    tree_referenced
    --Union
    Select
        tree_referencing.referenced_fullname
      , tree_referencing.referenced_fullname2
      , tree_referencing.Referenced_guid
      , tree_referencing.referenced_is_DocsOutput
      , tree_referencing.referenced_type
      , tree_referencing.referencing_fullname
      , tree_referencing.referencing_fullname2
      , tree_referencing.Referencing_guid
      , tree_referencing.referencing_type
      , tree_referencing.referencing_is_DocsOutput
      , tree_referencing.Referenced_Depth
      , tree_referencing.Referencing_Depth
      , RepoObject_guid = @RepoObject_guid
    From
        tree_referencing
);