repo.ForeignKey_Indexes - V
type: V ( view ), modify_date: 2022-01-05 18:06:35
RepoObject_guid: 7F170058-C073-EB11-84E3-A81E8446D5B0
Description
-
mapping from repo.ForeignKey_IndexPattern - V to referenced_index and referencing_indx
-
can be used to find out missing [referenced_index_guid] or [referencing_index_guid] to create them using repo.usp_Index_virtual_set - P
Columns
| PK | Column Name | Data Type | NULL? | ID |
|---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_ForeignKey_Indexes__1
-
IndexSemanticGroup: no_group
-
[column-ForeignKey_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_Indexes__2
-
IndexSemanticGroup: no_group
-
[column-referenced_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_Indexes__3
-
IndexSemanticGroup: no_group
-
[column-referencing_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_Indexes__4
-
IndexSemanticGroup: no_group
-
[column-referenced_SysObject_schema_name]; nvarchar(128)
-
[column-referencing_SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_Indexes__5
-
IndexSemanticGroup: no_group
-
[column-referencing_SysObject_schema_name]; nvarchar(128)
-
[column-referenced_SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
referenced_IndexPatternColumnDatatype
referenced_IndexPatternColumnDatatype |
|
|
referenced_IndexPatternColumnName
referenced_IndexPatternColumnName |
|
|
referenced_RepoObject_fullname
referenced_RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
referenced_RepoObject_fullname2
referenced_RepoObject_fullname2 |
|
|
(concat([RepoObject_schema_name],'.',[RepoObject_name]))
referenced_SysObject_schema_name
referenced_SysObject_schema_name |
|
|
referencing_IndexPatternColumnDatatype
referencing_IndexPatternColumnDatatype |
|
|
referencing_IndexPatternColumnName
referencing_IndexPatternColumnName |
|
|
referencing_RepoObject_fullname
referencing_RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
referencing_RepoObject_fullname2
referencing_RepoObject_fullname2 |
|
|
(concat([RepoObject_schema_name],'.',[RepoObject_name]))
referencing_SysObject_schema_name
referencing_SysObject_schema_name |
|
|
sql_modules_definition
repo.ForeignKey_Indexes - V script
/*
<<property_start>>Description
* mapping from xref:sqldb:repo.foreignkey_indexpattern.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_Indexes]
As
Select
--
fk.ForeignKey_guid
, is_MatchingDatatypePattern = Case
When i_1.IndexPatternColumnDatatype = i_2.IndexPatternColumnDatatype
Then
1
Else
0
End
, fk.ForeignKey_name
, fk.ForeignKey_fullname
, referenced_index_guid = i_2.index_guid
, referenced_index_name = i_2.index_name
, referenced_IndexPatternColumnDatatype = i_2.IndexPatternColumnDatatype
, fk.referenced_IndexPatternColumnName
, referenced_RepoObject_fullname = i_2.RepoObject_fullname
, referenced_RepoObject_fullname2 = i_2.RepoObject_fullname2
, fk.referenced_RepoObject_guid
, referenced_SysObject_name = i_2.SysObject_name
, referenced_SysObject_schema_name = i_2.SysObject_schema_name
, referencing_index_guid = i_1.index_guid
, referencing_index_name = i_1.index_name
, referencing_IndexPatternColumnDatatype = i_1.IndexPatternColumnDatatype
, fk.referencing_IndexPatternColumnName
, referencing_RepoObject_fullname = i_1.RepoObject_fullname
, referencing_RepoObject_fullname2 = i_1.RepoObject_fullname2
, fk.referencing_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_IndexPattern As fk
Left Join
repo.Index_gross As i_1
On
i_1.parent_RepoObject_guid = fk.referencing_RepoObject_guid
And i_1.IndexPatternColumnName = fk.referencing_IndexPatternColumnName
Left Join
repo.Index_gross As i_2
On
i_2.parent_RepoObject_guid = fk.referenced_RepoObject_guid
And i_2.IndexPatternColumnName = fk.referenced_IndexPatternColumnName