repo.ForeignKey_ssas_IndexPattern - V

type: V ( view ), modify_date: 2021-09-01 14:55:47

RepoObject_guid: 676E8DEE-3D08-EC11-8515-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo.foreignkey_ssas_indexpattern

Columns

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

nvarchar(128)

NOT NULL

tinyint

NULL

nvarchar(517)

NULL

uniqueidentifier

NOT NULL

nvarchar(263)

NULL

nvarchar(128)

NULL

nvarchar(500)

NULL

varchar(36)

NULL

nvarchar(128)

NULL

bit

NOT NULL

bit

NOT NULL

nvarchar(128)

NULL

nvarchar(517)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

nvarchar(500)

NULL

varchar(36)

NULL

nvarchar(128)

NULL

bit

NOT NULL

bit

NOT NULL

nvarchar(128)

NULL

nvarchar(517)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

nvarchar(500)

NULL

nvarchar(500)

NULL

bit

NOT NULL

nvarchar(500)

NULL

nvarchar(500)

NULL

tinyint

NULL

Foreign Key Diagram

entity_1_1_fk-repo.foreignkey_ssas_indexpattern

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.foreignkey_ssas_indexpattern

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.foreignkey_ssas_indexpattern

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.foreignkey_ssas_indexpattern

Column Reference Diagram

entity_1_1_colref-repo.foreignkey_ssas_indexpattern

Indexes

idx_ForeignKey_ssas_IndexPattern__1

idx_ForeignKey_ssas_IndexPattern__2

idx_ForeignKey_ssas_IndexPattern__3

idx_ForeignKey_ssas_IndexPattern__4

idx_ForeignKey_ssas_IndexPattern__5

idx_ForeignKey_ssas_IndexPattern__6

idx_ForeignKey_ssas_IndexPattern__7

idx_ForeignKey_ssas_IndexPattern__8

idx_ForeignKey_ssas_IndexPattern__9

Column Details

_

delete_referential_action

delete_referential_action

tinyint

NULL

ForeignKey_fullname

ForeignKey_fullname

nvarchar(517)

NULL

ForeignKey_guid

ForeignKey_guid

uniqueidentifier

NOT NULL

ForeignKey_name

ForeignKey_name

nvarchar(263)

NULL

referenced_ColumnName

referenced_ColumnName

nvarchar(128)

NULL

referenced_IndexPatternColumnDatatype

referenced_IndexPatternColumnDatatype

nvarchar(500)

NULL

referenced_IndexPatternColumnGuid

referenced_IndexPatternColumnGuid

varchar(36)

NULL

referenced_IndexPatternColumnName

referenced_IndexPatternColumnName

nvarchar(128)

NULL

referenced_IsKey

referenced_IsKey

bit

NOT NULL

referenced_IsNullable

referenced_IsNullable

bit

NOT NULL

referenced_ObjectName

referenced_ObjectName

nvarchar(128)

NULL

referenced_RepoObject_fullname

referenced_RepoObject_fullname

nvarchar(517)

NULL

referenced_RepoObject_fullname2

referenced_RepoObject_fullname2

nvarchar(257)

NULL

referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NULL

referencing_ColumnName

referencing_ColumnName

nvarchar(128)

NULL

referencing_IndexPatternColumnDatatype

referencing_IndexPatternColumnDatatype

nvarchar(500)

NULL

referencing_IndexPatternColumnGuid

referencing_IndexPatternColumnGuid

varchar(36)

NULL

referencing_IndexPatternColumnName

referencing_IndexPatternColumnName

nvarchar(128)

NULL

referencing_IsKey

referencing_IsKey

bit

NOT NULL

referencing_IsNullable

referencing_IsNullable

bit

NOT NULL

referencing_ObjectName

referencing_ObjectName

nvarchar(128)

NULL

referencing_RepoObject_fullname

referencing_RepoObject_fullname

nvarchar(517)

NULL

referencing_RepoObject_fullname2

referencing_RepoObject_fullname2

nvarchar(257)

NULL

referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NULL

relationships_crossFilteringBehavior

relationships_crossFilteringBehavior

nvarchar(500)

NULL

relationships_fromCardinality

relationships_fromCardinality

nvarchar(500)

NULL

relationships_isActive

relationships_isActive

bit

NOT NULL

relationships_name

relationships_name

nvarchar(500)

NULL

relationships_toCardinality

relationships_toCardinality

nvarchar(500)

NULL

update_referential_action

update_referential_action

tinyint

NULL

sql_modules_definition

repo.ForeignKey_ssas_IndexPattern - V script
CREATE View repo.ForeignKey_ssas_IndexPattern
As
Select
    fk.ForeignKey_guid
  , ForeignKey_name                        = 'FK_' + tFrom.tables_name + '_TO_' + tTo.tables_name
  , ForeignKey_fullname                    = QuoteName ( fk.databasename ) + '.'
                                             + QuoteName ( 'FK_' + tFrom.tables_name + '_TO_' + tTo.tables_name )
  , referenced_IndexPatternColumnName      = colTo.tables_columns_name
  , referenced_IndexPatternColumnGuid      = Cast(colTo.RepoObjectColumn_guid As Varchar(36))
  , referenced_RepoObject_guid             = tTo.RepoObject_guid
  , referencing_IndexPatternColumnName     = colFrom.tables_columns_name
  , referencing_IndexPatternColumnGuid     = Cast(colFrom.RepoObjectColumn_guid As Varchar(36))
  , referencing_RepoObject_guid            = tFrom.RepoObject_guid
  , delete_referential_action              = Cast(Null As TinyInt)
  , update_referential_action              = Cast(Null As TinyInt)
  --extra columns only in ssas
  , fk.databasename
  , fk.relationships_name
  , relationships_isActive                 = IsNull ( fk.relationships_isActive, 1 )
  --, fk.Type
  , fk.relationships_crossFilteringBehavior
  --, fk.JoinOnDateBehavior
  --, fk.RelyOnReferentialIntegrity
  , fk.relationships_fromCardinality
  , fk.relationships_toCardinality
  --, fk.SecurityFilteringBehavior
  , referenced_ObjectName                  = tTo.tables_name
  , referenced_ColumnName                  = colTo.tables_columns_name
  , referenced_IndexPatternColumnDatatype  = colTo.tables_columns_dataType
  , referenced_RepoObject_fullname         = QuoteName ( fk.databasename ) + '.' + QuoteName ( tTo.tables_name )
  , referenced_RepoObject_fullname2        = fk.databasename + '.' + tTo.tables_name
  , referenced_IsKey                       = IsNull ( colTo.tables_columns_isKey, 0 )
  , referenced_IsNullable                  = IsNull ( colTo.tables_columns_isNullable, 1 )
  , referencing_ObjectName                 = tFrom.tables_name
  , referencing_ColumnName                 = colFrom.tables_columns_name
  , referencing_IndexPatternColumnDatatype = colFrom.tables_columns_dataType
  , referencing_RepoObject_fullname        = QuoteName ( fk.databasename ) + '.' + QuoteName ( tFrom.tables_name )
  , referencing_RepoObject_fullname2       = fk.databasename + '.' + tFrom.tables_name
  , referencing_IsKey                      = IsNull ( colFrom.tables_columns_isKey, 0 )
  , referencing_IsNullable                 = IsNull ( colFrom.tables_columns_isNullable, 1 )
From
    ssas.model_json_32_relationships_T       As fk
    Left Join
        ssas.model_json_311_tables_columns_T As colFrom
            On
            colFrom.databasename            = fk.databasename
            And colFrom.tables_name         = fk.relationships_fromTable
            And colFrom.tables_columns_name = fk.relationships_fromColumn

    Left Join
        ssas.model_json_31_tables_T          As tFrom
            On
            tFrom.databasename              = colFrom.databasename
            And tFrom.tables_name           = colFrom.tables_name

    --Left Join
    --    configT.SsasDmv_ExplicitDataType As dtFrom
    --        On
    --        dtFrom.ExplicitDataType = colFrom.ExplicitDataType

    Left Join
        ssas.model_json_311_tables_columns_T As colTo
            On
            colTo.databasename              = fk.databasename
            And colTo.tables_name           = fk.relationships_toTable
            And colTo.tables_columns_name   = fk.relationships_toColumn

    Left Join
        ssas.model_json_31_tables_T          As tTo
            On
            tTo.databasename                = colTo.databasename
            And tTo.tables_name             = colTo.tables_name

--Left Join
--    configT.SsasDmv_ExplicitDataType As dtTo
--        On
--        dtTo.ExplicitDataType   = colTo.ExplicitDataType