repo.ForeignKey_ssas_Indexes - V
type: V ( view ), modify_date: 2021-10-02 13:06:36
RepoObject_guid: 686E8DEE-3D08-EC11-8515-A81E8446D5B0
Description
-
mapping from repo.ForeignKey_ssas_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_ssas_Indexes__1
-
IndexSemanticGroup: no_group
-
[column-referenced_index_name]; nvarchar(450)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__10
-
IndexSemanticGroup: no_group
-
[column-referenced_IndexPatternColumnName]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__11
-
IndexSemanticGroup: no_group
-
[column-ForeignKey_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__2
-
IndexSemanticGroup: no_group
-
[column-referencing_index_name]; nvarchar(450)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__3
-
IndexSemanticGroup: no_group
-
[column-referencing_SysObject_schema_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__4
-
IndexSemanticGroup: no_group
-
[column-referenced_SysObject_schema_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__5
-
IndexSemanticGroup: no_group
-
[column-referenced_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__6
-
IndexSemanticGroup: no_group
-
[column-referencing_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__7
-
IndexSemanticGroup: no_group
-
[column-referencing_SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__8
-
IndexSemanticGroup: no_group
-
[column-referenced_SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_ForeignKey_ssas_Indexes__9
-
IndexSemanticGroup: no_group
-
[column-referencing_IndexPatternColumnName]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
referenced_IndexPatternColumnDatatype
referenced_IndexPatternColumnDatatype |
|
|
referenced_IndexPatternColumnName
referenced_IndexPatternColumnName |
|
|
referenced_RepoObject_fullname2
referenced_RepoObject_fullname2 |
|
|
referenced_SysObject_schema_name
referenced_SysObject_schema_name |
|
|
referencing_IndexPatternColumnDatatype
referencing_IndexPatternColumnDatatype |
|
|
referencing_IndexPatternColumnName
referencing_IndexPatternColumnName |
|
|
referencing_RepoObject_fullname
referencing_RepoObject_fullname |
|
|
referencing_RepoObject_fullname2
referencing_RepoObject_fullname2 |
|
|
referencing_SysObject_schema_name
referencing_SysObject_schema_name |
|
|
sql_modules_definition
repo.ForeignKey_ssas_Indexes - V script
/*
<<property_start>>Description
* mapping from xref:sqldb:repo.foreignkey_ssas_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_ssas_Indexes]
As
Select
fk.ForeignKey_guid
, is_MatchingDatatypePattern = Iif(
fk.referencing_IndexPatternColumnDatatype = fk.referenced_IndexPatternColumnDatatype
, 1
, 0)
, fk.ForeignKey_name
, fk.ForeignKey_fullname
, referenced_index_guid = i_2.index_guid
, referenced_index_name = i_2.index_name
, fk.referenced_IndexPatternColumnDatatype
, fk.referenced_IndexPatternColumnName
, fk.referenced_RepoObject_fullname
, fk.referenced_RepoObject_fullname2
, fk.referenced_RepoObject_guid
, referenced_SysObject_name = fk.referenced_ObjectName
, referenced_SysObject_schema_name = fk.databasename
, referencing_index_guid = i_1.index_guid
, referencing_index_name = i_1.index_name
, fk.referencing_IndexPatternColumnDatatype
, fk.referencing_IndexPatternColumnName
, fk.referencing_RepoObject_fullname
, fk.referencing_RepoObject_fullname2
, fk.referencing_RepoObject_guid
, referencing_SysObject_name = fk.referencing_ObjectName
, referencing_SysObject_schema_name = fk.databasename
, fk.delete_referential_action
, fk.update_referential_action
From
repo.ForeignKey_ssas_IndexPattern As fk
Left Join
repo.Index_ssas_T As i_1
On
i_1.RepoObject_guid = fk.referencing_RepoObject_guid
And i_1.ColumnName = fk.referencing_ColumnName
Left Join
repo.Index_ssas_T As i_2
On
i_2.RepoObject_guid = fk.referenced_RepoObject_guid
And i_2.ColumnName = fk.referenced_ColumnName