reference.RepoObjectColumn_reference_BySamePredecessors - V

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

RepoObject_guid: 7390291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-reference.repoobjectcolumn_reference_bysamepredecessors

Columns

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

nvarchar(max)

NULL

varchar(37)

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

int

NULL

int

NULL

bigint

NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

char(2)

NULL

nvarchar(128)

NULL

nvarchar(128)

NOT NULL

int

NULL

int

NULL

bigint

NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NULL

nvarchar(128)

NOT NULL

char(2)

NULL

Foreign Key Diagram

entity_1_1_fk-reference.repoobjectcolumn_reference_bysamepredecessors

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.repoobjectcolumn_reference_bysamepredecessors

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.repoobjectcolumn_reference_bysamepredecessors

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.repoobjectcolumn_reference_bysamepredecessors

Column Reference Diagram

entity_1_1_colref-reference.repoobjectcolumn_reference_bysamepredecessors

Indexes

idx_RepoObjectColumn_reference_BySamePredecessors__1

idx_RepoObjectColumn_reference_BySamePredecessors__2

idx_RepoObjectColumn_reference_BySamePredecessors__3

idx_RepoObjectColumn_reference_BySamePredecessors__4

idx_RepoObjectColumn_reference_BySamePredecessors__5

Column Details

_

definition

definition

nvarchar(max)

NULL

InformationSource

InformationSource

varchar(37)

NOT NULL

is_computed

is_computed

bit

NULL

is_hidden

is_hidden

bit

NULL

is_referenced_object

is_referenced_object

bit

NULL

is_referencing_object_equal_referenced_object

is_referencing_object_equal_referenced_object

bit

NULL

referenced_column_name

referenced_column_name

nvarchar(128)

NULL

referenced_entity_name

referenced_entity_name

nvarchar(128)

NULL

referenced_id

referenced_id

int

NULL

referenced_minor_id

referenced_minor_id

int

NULL

referenced_node_id

referenced_node_id

bigint

NULL

referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NOT NULL

referenced_RepoObjectColumn_guid

referenced_RepoObjectColumn_guid

uniqueidentifier

NULL

referenced_schema_name

referenced_schema_name

nvarchar(128)

NULL

referenced_type

referenced_type

char(2)

NULL

referencing_column_name

referencing_column_name

nvarchar(128)

NULL

referencing_entity_name

referencing_entity_name

nvarchar(128)

NOT NULL

referencing_id

referencing_id

int

NULL

referencing_minor_id

referencing_minor_id

int

NULL

referencing_node_id

referencing_node_id

bigint

NULL

referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NOT NULL

referencing_RepoObjectColumn_guid

referencing_RepoObjectColumn_guid

uniqueidentifier

NULL

referencing_schema_name

referencing_schema_name

nvarchar(128)

NOT NULL

referencing_type

referencing_type

char(2)

NULL

sql_modules_definition

reference.RepoObjectColumn_reference_BySamePredecessors - V script
/*
repo.RepoObjectColumn_reference__first_result_set
for view columns the referenced columns in a predecessor table is shown, not the referenced colum in a predecessor view
but we are looking for the referenced column in a predecessor view

example

create view view_1
as
select
aaa
from table_1

create view view_2
as
select
aaa
from view_1

repo.RepoObjectColumn_reference__first_result_set result in 2 columns references
referencing -> referenced

dbo.view_1.aaa -> dbo.table_1.aaa : roc_r_t1
dbo.view_2.aaa -> dbo.table_1.aaa : roc_r_t2


we combine with object reference ro_r

view_2 -> view_1

We are looking for common predecessors:
dbo.table_1.aaa

and we get what we need:
dbo.view_2.aaa -> dbo.view_1.aaa

*/
CREATE View reference.RepoObjectColumn_reference_BySamePredecessors
As
--
Select
    roc_r_t2.referencing_id
  , roc_r_t2.referencing_minor_id
  , roc_r_t2.referencing_node_id
  , roc_r_t2.referencing_RepoObject_guid
  , roc_r_t2.referencing_RepoObjectColumn_guid
  , roc_r_t2.referencing_type
  , roc_r_t2.referencing_schema_name
  , roc_r_t2.referencing_entity_name
  , roc_r_t2.referencing_column_name
  , referenced_id                                 = roc_r_t1.referencing_id
  , referenced_minor_id                           = roc_r_t1.referencing_minor_id
  , referenced_node_id                            = roc_r_t1.referencing_node_id
  , ro_r.referenced_RepoObject_guid
  , referenced_RepoObjectColumn_guid              = roc_r_t1.referencing_RepoObjectColumn_guid
  , ro_r.referenced_schema_name
  , ro_r.referenced_entity_name
  , referenced_column_name                        = roc_r_t1.referencing_column_name
  , ro_r.referenced_type
  , roc_r_t2.InformationSource
  , roc_r_t2.is_hidden
  , is_computed                                   = Cast(0 As Bit)
  , definition                                    = Cast(Null As NVarchar(Max))
  , is_referencing_object_equal_referenced_object = Cast(Case
                                                             When roc_r_t2.referencing_RepoObject_guid = ro_r.referenced_RepoObject_guid
                                                                 Then
                                                                 1
                                                             Else
                                                                 0
                                                         End As Bit)
  , is_referenced_object                          = Cast(1 As Bit)
From
    reference.RepoObject_reference_T                        As ro_r
    Inner Join
        reference.RepoObjectColumn_reference_FirstResultSet As roc_r_t2
            On
            ro_r.referencing_RepoObject_guid              = roc_r_t2.referencing_RepoObject_guid

    Inner Join
        reference.RepoObjectColumn_reference_FirstResultSet As roc_r_t1
            On
            ro_r.referenced_RepoObject_guid               = roc_r_t1.referencing_RepoObject_guid
            And roc_r_t2.referenced_RepoObject_guid       = roc_r_t1.referenced_RepoObject_guid
            And roc_r_t2.referenced_RepoObjectColumn_guid = roc_r_t1.referenced_RepoObjectColumn_guid
--we don't care about [is_hidden] columns or other possible not required entries
--and to ensure all results can be used we ensure RepoObjectColum exists
Where
    Not roc_r_t2.referencing_RepoObjectColumn_guid Is Null
    And Not roc_r_t1.referencing_RepoObjectColumn_guid Is Null