reference.ftv_RepoObject_ColumReferenceRepoObject - IF

type: IF ( inline function ), modify_date: 2022-01-05 18:06:35

RepoObject_guid: 9BBE898F-9381-EB11-84E9-A81E8446D5B0

Description

Examples

Parameters

  • @RepoObject_guid (uniqueidentifier)

  • @Referenced_Depth (int)

  • @Referencing_Depth (int)

Entity Diagram

entity-reference.ftv_repoobject_columreferencerepoobject

Columns

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

nvarchar(max)

NULL

uniqueidentifier

NULL

nvarchar(261)

NULL

uniqueidentifier

NULL

char(2)

NULL

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.ftv_repoobject_columreferencerepoobject

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.ftv_repoobject_columreferencerepoobject

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.ftv_repoobject_columreferencerepoobject

Column Reference Diagram

entity_1_1_colref-reference.ftv_repoobject_columreferencerepoobject

Column Details

_

Dbml

Dbml

nvarchar(max)

NULL

Parameter_RepoObject_guid

Parameter_RepoObject_guid

uniqueidentifier

NULL

RepoObject_fullname

RepoObject_fullname

nvarchar(261)

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NULL

RepoObject_type

RepoObject_type

char(2)

NULL

sql_modules_definition

reference.ftv_RepoObject_ColumReferenceRepoObject - IF script
/*
--hier stimmt irgendetwas nicht, es dauert sehr lange und es kommt ein Fehler mit einer Typ-Konvertierung

--based on [repo].[RepoObjectColumn_ReferenceTree]
--return referenced and referencing RepoObject
--Default:
--@Referenced_Depth = 1
--@Referencing_Depth

DECLARE @RepoObject_guid uniqueidentifier

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

SELECT *
FROM [reference].[ftv_RepoObject_ColumReferenceRepoObject](@RepoObject_guid, DEFAULT, DEFAULT)

SELECT *
FROM [reference].[ftv_RepoObject_ColumReferenceRepoObject](@RepoObject_guid, 1, 1)

*/
CREATE Function reference.ftv_RepoObject_ColumReferenceRepoObject
(
    @RepoObject_guid   UniqueIdentifier
  , @Referenced_Depth  Int = 1
  , @Referencing_Depth Int = 1
)
Returns Table
As
Return
(
    With
    ro
    As
        (
        --all RepoObject which are [Referenced_RepoObject_guid] or [Referencing_RepoObject_guid]
        Select
            Distinct
            RepoObject_fullname       = Referenced_fullname
          , RepoObject_guid           = Referenced_RepoObject_guid
          , RepoObject_type           = Referenced_type
          , DbmlRelation              = Null
          , Parameter_RepoObject_guid = @RepoObject_guid
        From
            reference.RepoObjectColumn_ReferenceTree
        Where
            Referenced_RepoObject_guid = @RepoObject_guid
        Union
        Select
            Distinct
            Referencing_fullname
          , Referencing_RepoObject_guid
          , Referencing_type
          , DbmlRelation               = Null
          , @RepoObject_guid
        From
            reference.RepoObjectColumn_ReferenceTree
        Where
            Referencing_RepoObject_guid = @RepoObject_guid
        Union

        --add all referenced
        Select
            Distinct
            rt.Referenced_fullname
          , rt.Referenced_RepoObject_guid
          , rt.Referenced_type
          , rs.DbmlRelation
          , @RepoObject_guid
        From
            reference.RepoObjectColumn_ReferenceTree      As rt
            Left Join
                reference.RepoObjectColumn_RelationScript As rs
                    On
                    rs.referenced_RepoObject_guid      = rt.Referenced_RepoObject_guid
                    And rs.referencing_RepoObject_guid = @RepoObject_guid
        Where
            rt.Referencing_RepoObject_guid = @RepoObject_guid
            And rt.Referenced_Depth        <= @Referenced_Depth
            And rt.Referencing_Depth       = 0
        Union

        --add all referenced
        Select
            Distinct
            rt.Referencing_fullname
          , rt.Referencing_RepoObject_guid
          , rt.Referencing_type
          , rs.DbmlRelation
          , @RepoObject_guid
        From
            reference.RepoObjectColumn_ReferenceTree      As rt
            Left Join
                reference.RepoObjectColumn_RelationScript As rs
                    On
                    rs.referenced_RepoObject_guid      = @RepoObject_guid
                    And rs.referencing_RepoObject_guid = rt.Referencing_RepoObject_guid
        Where
            rt.Referenced_RepoObject_guid = @RepoObject_guid
            And rt.Referenced_Depth       = 0
            And rt.Referencing_Depth      <= @Referencing_Depth
        )
    --
    Select
        ro.RepoObject_fullname
      , ro.RepoObject_guid
      , ro.RepoObject_type
      , Dbml = dbml.DbmlTable
      , ro.Parameter_RepoObject_guid
    From
        ro
        Left Join
            repo.RepoObject_SqlCreateTable As dbml
                On
                dbml.RepoObject_guid = ro.RepoObject_guid
    Union
    Select
        Null
      , ro.Parameter_RepoObject_guid
      , Null
      , ro.DbmlRelation
      , ro.Parameter_RepoObject_guid
    From
        ro
    Where
        Not ro.DbmlRelation Is Null
--
);