reference.RepoObjectColumn_RelationScript - V

type: V ( view ), modify_date: 2022-01-05 18:06:33

RepoObject_guid: 06DF2FE1-AE7A-EB11-84E5-A81E8446D5B0

Description

Examples

Entity Diagram

entity-reference.repoobjectcolumn_relationscript

Columns

Table 1. Columns of reference.RepoObjectColumn_RelationScript - V
PK Column Name Data Type NULL? ID

nvarchar(786)

NOT NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

Foreign Key Diagram

entity_1_1_fk-reference.repoobjectcolumn_relationscript

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.repoobjectcolumn_relationscript

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.repoobjectcolumn_relationscript

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.repoobjectcolumn_relationscript

Column Reference Diagram

entity_1_1_colref-reference.repoobjectcolumn_relationscript

Column Details

_

DbmlRelation

DbmlRelation

nvarchar(786)

NOT NULL

referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NULL

referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NULL

sql_modules_definition

reference.RepoObjectColumn_RelationScript - V script
CREATE View reference.RepoObjectColumn_RelationScript
As
--
Select
    Distinct
    --
    rocu.referenced_RepoObject_guid
  , rocu.referencing_RepoObject_guid
  , DbmlRelation = Concat (
                              'Ref'
                            , ': '
                            , QuoteName (
                                            QuoteName ( rocu.referencing_schema_name ) + '.'
                                            + QuoteName ( rocu.referencing_entity_name )
                                          , '"'
                                        )
                            , '."'
                            , rocu.referencing_column_name
                            , '"'
                            --<: one-to-many. E.g: users.id < posts.user_id
                            -->: many-to-one. E.g: posts.user_id > users.id
                            ---: one-to-one. E.g: users.id - user_infos.user_id
                            , ' > '
                            , QuoteName (
                                            QuoteName ( rocu.referenced_schema_name ) + '.'
                                            + QuoteName ( rocu.referenced_entity_name )
                                          , '"'
                                        )
                            , '."'
                            , rocu.referenced_column_name
                            , '"'
                          )
From
    reference.RepoObjectColumn_reference_T As rocu
    Inner Join
        repo.RepoObjectColumn              As roc1
            On
            roc1.RepoObjectColumn_guid = rocu.referencing_RepoObjectColumn_guid

    Inner Join
        repo.RepoObjectColumn              As roc2
            On
            roc2.RepoObjectColumn_guid = rocu.referenced_RepoObjectColumn_guid
Where
    --exclude virtual columns, which have [uniqueidentifier] as name
    roc1.is_RepoObjectColumn_name_uniqueidentifier     = 0
    And roc2.is_RepoObjectColumn_name_uniqueidentifier = 0
    And roc1.is_SysObjectColumn_name_uniqueidentifier  = 0
    And roc2.is_SysObjectColumn_name_uniqueidentifier  = 0
--AND NOT [referenced_RepoObjectColumn_guid] IS NULL
--AND NOT [referencing_RepoObjectColumn_guid] IS NULL