ssas.IndexColumn_from_IsKey - V

type: V ( view ), modify_date: 2021-09-01 13:37:16

RepoObject_guid: 979CF1C3-0B08-EC11-8515-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssas.indexcolumn_from_iskey

Columns

Table 1. Columns of ssas.IndexColumn_from_IsKey - V
PK Column Name Data Type NULL? ID

nvarchar(128)

NOT NULL

int

NOT NULL

nvarchar(265)

NOT NULL

int

NOT NULL

bit

NOT NULL

bit

NOT NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(500)

NULL

Foreign Key Diagram

entity_1_1_fk-ssas.indexcolumn_from_iskey

References

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-ssas.indexcolumn_from_iskey

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssas.indexcolumn_from_iskey

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssas.indexcolumn_from_iskey

Column Reference Diagram

entity_1_1_colref-ssas.indexcolumn_from_iskey

Indexes

idx_IndexColumn_from_IsKey__1

idx_IndexColumn_from_IsKey__2

idx_IndexColumn_from_IsKey__3

Column Details

_

databasename

databasename

nvarchar(128)

NOT NULL

index_column_id

index_column_id

int

NOT NULL

index_name

index_name

nvarchar(265)

NOT NULL

is_descending_key

is_descending_key

int

NOT NULL

is_index_primary_key

is_index_primary_key

bit

NOT NULL

is_index_unique

is_index_unique

bit

NOT NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

RepoObjectColumn_guid

RepoObjectColumn_guid

uniqueidentifier

NOT NULL

TableName

TableName

nvarchar(128)

NOT NULL

tables_columns_name

tables_columns_name

nvarchar(128)

NOT NULL

tables_columns_type

tables_columns_type

nvarchar(500)

NULL

sql_modules_definition

ssas.IndexColumn_from_IsKey - V script
/*
create index from each ssas column, marked with IsKey = 1
*/
CREATE View ssas.IndexColumn_from_IsKey
As
Select
    index_name           = Concat (   'key_'
                                    --, Row_Number () Over ( Partition By T2.databasename, T2.TableID Order By T2.ExplicitName )
                                    --, '_'
                                    , T2.tables_name
                                    , '_col_'
                                    , T2.tables_columns_name
                                  )
  , index_column_id      = 1 --one column per index => explicite value 1
  , T2.RepoObjectColumn_guid
  , is_descending_key    = 0
  , is_index_primary_key = IsNull( T2.tables_columns_isKey, 0)
  , is_index_unique      = IsNull ( T2.tables_columns_isUnique, 0 )
  , T2.databasename
  , T2.tables_columns_name
  , T2.tables_columns_type
  , T2.RepoObject_guid
  , TableName            = T2.tables_name
From
    ssas.model_json_311_tables_columns_T As T2
Where
    T2.tables_columns_isKey = 1