repo.IndexReferencedReferencing_HasFullColumnsInReferencing - V

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

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

Description

Examples

Entity Diagram

entity-repo.indexreferencedreferencing_hasfullcolumnsinreferencing

Columns

Table 1. Columns of repo.IndexReferencedReferencing_HasFullColumnsInReferencing - V
PK Column Name Data Type NULL? ID

int

NULL

uniqueidentifier

NULL

uniqueidentifier

NOT NULL

varchar(8000)

NULL

uniqueidentifier

NOT NULL

bigint

NULL

int

NULL

uniqueidentifier

NULL

tinyint

NULL

Foreign Key Diagram

entity_1_1_fk-repo.indexreferencedreferencing_hasfullcolumnsinreferencing

References

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.indexreferencedreferencing_hasfullcolumnsinreferencing

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.indexreferencedreferencing_hasfullcolumnsinreferencing

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.indexreferencedreferencing_hasfullcolumnsinreferencing

Column Reference Diagram

entity_1_1_colref-repo.indexreferencedreferencing_hasfullcolumnsinreferencing

Indexes

idx_IndexReferencedReferencing_HasFullColumnsInReferencing__1

idx_IndexReferencedReferencing_HasFullColumnsInReferencing__2

idx_IndexReferencedReferencing_HasFullColumnsInReferencing__3

Column Details

_

ColumnsPerIndex

ColumnsPerIndex

int

NULL

referenced_index_guid

referenced_index_guid

uniqueidentifier

NULL

referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NOT NULL

referencing_IndexPatternColumnGuid

referencing_IndexPatternColumnGuid

varchar(8000)

NULL

referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NOT NULL

RowNumberInReferencing

RowNumberInReferencing

bigint

NULL

RowNumberInReferencing_Target

RowNumberInReferencing_Target

int

NULL

source_index_guid

source_index_guid

uniqueidentifier

NULL

source_index_type

source_index_type

tinyint

NULL

sql_modules_definition

repo.IndexReferencedReferencing_HasFullColumnsInReferencing - V script
/*
HasFullColumnsInReferencing:
Filter repo.IndexReferencedReferencing
all columns, existing in referenced, should also exist in referencing
in other words, the referenced index is completely contained in the referencing object

keep in mind, that a [source_index_guid] can be inherited into several [referenced_index_guid]
if the source object is used several times but target columns are different

Thats why we have [RowNumberInReferencing] and [RowNumberInReferencing_Target]

But [RowNumberInReferencing_Target] is a bit hard to understand. it is the [RowNumberInReferencing] stored before (in earlier runs) into [repo].[Index_virtual]
the same index can be inherited several times into the same referenced object, if a source is used several times
for example
SELECT A_A = A.A, B_A = B.A from source_1 as A LEFT JOIN source_1 as B ON ...
normaly these indexes should have different columns

Issue:
if [repo].[Index_virtual].[referenced_index_guid] is missing, then it could be contained in repo.IndexReferencedReferencing
but [referenced_index_guid] is NULL in this case

How we could / should create a missing but possible [repo].[Index_virtual].[referenced_index_guid]?
=> in usp_index_inheritance
not only insert, but also update of [repo].[Index_virtual].[referenced_index_guid] if it is NULL but it has a source_index here in this view
*/
CREATE View repo.IndexReferencedReferencing_HasFullColumnsInReferencing
As
--
Select
    T1.source_index_guid
  , T1.referencing_RepoObject_guid
  , T1.RowNumberInReferencing_Target
  , T1.source_index_type
  , T1.referenced_RepoObject_guid
  , T1.referenced_index_guid
  , T2.RowNumberInReferencing
  , T2.ColumnsPerIndex
  , T3.referencing_IndexPatternColumnGuid
From
    repo.IndexReferencedReferencing                                          As T1
    Inner Join
        repo.IndexColumn_ReferencedReferencing_HasFullColumnsInReferencing_T As T2
            On
            T1.source_index_guid               = T2.index_guid
            And T1.referenced_RepoObject_guid  = T2.referenced_RepoObject_guid
            And T1.referencing_RepoObject_guid = T2.referencing_RepoObject_guid

    Left Join
        repo.Index_referencing_IndexPatternColumnGuid                        As T3
            On
            T3.source_index_guid               = T1.source_index_guid
            And T3.referencing_RepoObject_guid = T1.referencing_RepoObject_guid