repo.ForeignKey_virtual_Indexes - V

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

RepoObject_guid: 80170058-C073-EB11-84E3-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo.foreignkey_virtual_indexes

Columns

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

tinyint

NOT NULL

nvarchar(517)

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NULL

int

NOT NULL

uniqueidentifier

NULL

nvarchar(450)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

uniqueidentifier

NULL

nvarchar(450)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

tinyint

NOT NULL

Foreign Key Diagram

entity_1_1_fk-repo.foreignkey_virtual_indexes

References

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.foreignkey_virtual_indexes

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.foreignkey_virtual_indexes

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.foreignkey_virtual_indexes

Column Reference Diagram

entity_1_1_colref-repo.foreignkey_virtual_indexes

Indexes

idx_ForeignKey_virtual_Indexes__1

idx_ForeignKey_virtual_Indexes__2

idx_ForeignKey_virtual_Indexes__3

idx_ForeignKey_virtual_Indexes__4

idx_ForeignKey_virtual_Indexes__5

Column Details

_

delete_referential_action

delete_referential_action

tinyint

NOT NULL

ForeignKey_fullname

ForeignKey_fullname

nvarchar(517)

NOT NULL

ForeignKey_guid

ForeignKey_guid

uniqueidentifier

NOT NULL

ForeignKey_name

ForeignKey_name

nvarchar(128)

NULL

is_MatchingDatatypePattern

is_MatchingDatatypePattern

int

NOT NULL

referenced_index_guid

referenced_index_guid

uniqueidentifier

NULL

referenced_index_name

referenced_index_name

nvarchar(450)

NULL

Referenced Columns

referenced_IndexPatternColumnDatatype

referenced_IndexPatternColumnDatatype

nvarchar(4000)

NULL

referenced_IndexPatternColumnName

referenced_IndexPatternColumnName

nvarchar(4000)

NULL

referenced_RepoObject_fullname

referenced_RepoObject_fullname

nvarchar(261)

NULL

Description

(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))


referenced_RepoObject_fullname2

referenced_RepoObject_fullname2

nvarchar(257)

NULL

Description

(concat([RepoObject_schema_name],'.',[RepoObject_name]))


referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NULL

referenced_SysObject_name

referenced_SysObject_name

nvarchar(128)

NULL

Referenced Columns

referenced_SysObject_schema_name

referenced_SysObject_schema_name

nvarchar(128)

NULL

referencing_index_guid

referencing_index_guid

uniqueidentifier

NULL

referencing_index_name

referencing_index_name

nvarchar(450)

NULL

Referenced Columns

referencing_IndexPatternColumnDatatype

referencing_IndexPatternColumnDatatype

nvarchar(4000)

NULL

referencing_IndexPatternColumnName

referencing_IndexPatternColumnName

nvarchar(4000)

NULL

referencing_RepoObject_fullname

referencing_RepoObject_fullname

nvarchar(261)

NULL

Description

(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))


referencing_RepoObject_fullname2

referencing_RepoObject_fullname2

nvarchar(257)

NULL

Description

(concat([RepoObject_schema_name],'.',[RepoObject_name]))


referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NULL

referencing_SysObject_name

referencing_SysObject_name

nvarchar(128)

NULL

Referenced Columns

referencing_SysObject_schema_name

referencing_SysObject_schema_name

nvarchar(128)

NULL

update_referential_action

update_referential_action

tinyint

NOT NULL

sql_modules_definition

repo.ForeignKey_virtual_Indexes - V script
/*
<<property_start>>Description
* mapping from xref:sqldb:repo.foreignkey_virtual.adoc[] to referenced_index and referencing_indx
* can be used to find out missing [referenced_index_guid] or [referencing_index_guid] to create them using xref:sqldb:repo.usp_index_virtual_set.adoc[]
<<property_end>>
*/

CREATE View [repo].[ForeignKey_virtual_Indexes]
As
Select
    --
    fk.ForeignKey_guid
  , is_MatchingDatatypePattern             = Case
                                                 When i_1.IndexPatternColumnDatatype = i_2.IndexPatternColumnDatatype
                                                     Then
                                                     1
                                                 Else
                                                     0
                                             End
  , fk.ForeignKey_name
  , ForeignKey_fullname                    = Concat ( QuoteName ( i_1.SysObject_schema_name ), '.', QuoteName ( fk.ForeignKey_name ))
  , fk.referenced_index_guid
  , referenced_index_name                  = i_2.index_name
  , referenced_IndexPatternColumnDatatype  = i_2.IndexPatternColumnDatatype
  , referenced_IndexPatternColumnName      = i_2.IndexPatternColumnName
  , referenced_RepoObject_fullname         = i_2.RepoObject_fullname
  , referenced_RepoObject_fullname2        = i_2.RepoObject_fullname2
  , referenced_RepoObject_guid             = i_2.parent_RepoObject_guid
  , referenced_SysObject_name              = i_2.SysObject_name
  , referenced_SysObject_schema_name       = i_2.SysObject_schema_name
  , fk.referencing_index_guid
  , referencing_index_name                 = i_1.index_name
  , referencing_IndexPatternColumnDatatype = i_1.IndexPatternColumnDatatype
  , referencing_IndexPatternColumnName     = i_1.IndexPatternColumnName
  , referencing_RepoObject_fullname        = i_1.RepoObject_fullname
  , referencing_RepoObject_fullname2       = i_1.RepoObject_fullname2
  , referencing_RepoObject_guid            = i_1.parent_RepoObject_guid
  , referencing_SysObject_name             = i_1.SysObject_name
  , referencing_SysObject_schema_name      = i_1.SysObject_schema_name
  , fk.delete_referential_action
  , fk.update_referential_action
From
    repo.ForeignKey_virtual As fk
    Left Join
        repo.Index_gross    As i_1
            On
            i_1.index_guid = fk.referencing_index_guid

    Left Join
        repo.Index_gross    As i_2
            On
            i_2.index_guid = fk.referenced_index_guid