reference.ftv_RepoObject_ReferenceTree - IF

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

RepoObject_guid: 0ADF2FE1-AE7A-EB11-84E5-A81E8446D5B0

Description

Duplicates are possible, if exists alternative path between objects with different depth
to elimenate them, exclude Referenced_Depth and Referencing_Depth and use DISTINCT

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](@RepoObject_guid, DEFAULT, DEFAULT)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

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

-- Starting from @RepoObject_guid the result contains only referencing objects up to a depth of 6
-- start node is @RepoObject_guid, end node is Referencing_...

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

-- Starting from @RepoObject_guid the result contains only referenced objects up to a depth of 30
-- start node is @RepoObject_guid, end node is Referenced_...

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

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

--The next query is very slow!

SELECT *
FROM [reference].[ftv_RepoObject_ReferenceTree](@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

Columns

Table 1. Columns of reference.ftv_RepoObject_ReferenceTree - 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

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.ftv_repoobject_referencetree

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.ftv_repoobject_referencetree

Column Reference Diagram

entity_1_1_colref-reference.ftv_repoobject_referencetree

Column Details

_

Referenced_Depth

Referenced_Depth

int

NULL

has_get_referenced_issue
1

Referenced_fullname

Referenced_fullname

nvarchar(261)

NULL

has_get_referenced_issue
1

Referenced_fullname2

Referenced_fullname2

nvarchar(257)

NULL

has_get_referenced_issue
1

Referenced_guid

Referenced_guid

uniqueidentifier

NULL

has_get_referenced_issue
1

referenced_is_DocsOutput

referenced_is_DocsOutput

int

NULL

has_get_referenced_issue
1

Referenced_type

Referenced_type

char(2)

NULL

has_get_referenced_issue
1

Referencing_Depth

Referencing_Depth

int

NULL

has_get_referenced_issue
1

Referencing_fullname

Referencing_fullname

nvarchar(261)

NULL

has_get_referenced_issue
1

Referencing_fullname2

Referencing_fullname2

nvarchar(257)

NULL

has_get_referenced_issue
1

Referencing_guid

Referencing_guid

uniqueidentifier

NULL

has_get_referenced_issue
1

referencing_is_DocsOutput

referencing_is_DocsOutput

int

NULL

has_get_referenced_issue
1

Referencing_type

Referencing_type

varchar(2)

NULL

has_get_referenced_issue
1

RepoObject_guid

RepoObject_guid

uniqueidentifier

NULL

has_get_referenced_issue
1

sql_modules_definition

reference.ftv_RepoObject_ReferenceTree - IF script
/*
<<property_start>>Description
NOTE: 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_end>>

<<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](@RepoObject_guid, DEFAULT, DEFAULT)
ORDER BY [Referenced_Depth]
 , [Referencing_Depth]

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

-- Starting from @RepoObject_guid the result contains only referencing objects up to a depth of 6
-- start node is @RepoObject_guid, end node is Referencing_...

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

-- Starting from @RepoObject_guid the result contains only referenced objects up to a depth of 30
-- start node is @RepoObject_guid, end node is Referenced_...

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

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

--The next query is very slow!

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


*/
CREATE Function reference.ftv_RepoObject_ReferenceTree
(
    @RepoObject_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_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  = 1
          , Referencing_Depth = 0
        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.Referencing_guid = @RepoObject_guid
            And 1                      <= @Referenced_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
          , Referenced_Depth = parent.Referenced_Depth + 1
          , 0
        From
            reference.RepoObject_ReferencedReferencing 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
        tree_referenced.referenced_fullname
      , tree_referenced.referenced_fullname2
      , tree_referenced.Referenced_guid
      , tree_referenced.referenced_is_DocsOutput
      , tree_referenced.referenced_type
      , tree_referenced.referencing_fullname
      , tree_referenced.referencing_fullname2
      , tree_referenced.Referencing_guid
      , tree_referenced.referencing_type
      , tree_referenced.referencing_is_DocsOutput
      , tree_referenced.Referenced_Depth
      , tree_referenced.Referencing_Depth
      , RepoObject_guid = @RepoObject_guid
    From
        tree_referenced
    Union All
    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
);