repo.Index_gross - V

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

RepoObject_guid: 8790291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo.index_gross

Columns

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

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

uniqueidentifier

NOT NULL

nvarchar(450)

NULL

tinyint

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(512)

NULL

bit

NOT NULL

int

NULL

bit

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

uniqueidentifier

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

uniqueidentifier

NULL

nvarchar(261)

NOT NULL

nvarchar(257)

NOT NULL

bigint

NULL

bigint

NULL

nvarchar(261)

NOT NULL

nvarchar(257)

NOT NULL

int

NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

char(2)

NULL

Foreign Key Diagram

entity_1_1_fk-repo.index_gross

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.index_gross

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.index_gross

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.index_gross

Column Reference Diagram

entity_1_1_colref-repo.index_gross

Indexes

idx_Index_gross__1

idx_Index_gross__2

Column Details

_

AntoraIndexColumnList

AntoraIndexColumnList

nvarchar(max)

NULL

ColumnList

ColumnList

nvarchar(max)

NULL

ConstraintColumnList

ConstraintColumnList

nvarchar(max)

NULL

DbmlIndexColumnList

DbmlIndexColumnList

nvarchar(max)

NULL

index_guid

index_guid

uniqueidentifier

NOT NULL

Referenced Columns

index_type

index_type

tinyint

NULL

Referenced Columns

IndexSemanticGroup

IndexSemanticGroup

nvarchar(512)

NULL

is_DocsExclude

is_DocsExclude

bit

NOT NULL

is_DocsOutput

is_DocsOutput

int

NULL

is_external

is_external

bit

NOT NULL

is_index_disabled

is_index_disabled

bit

NULL

is_index_primary_key

is_index_primary_key

bit

NULL

is_index_real

is_index_real

bit

NULL

Referenced Columns

is_index_unique

is_index_unique

bit

NULL

is_persistence

is_persistence

bit

NULL

is_repo_managed

is_repo_managed

bit

NULL

PersistenceWhereColumnList

PersistenceWhereColumnList

nvarchar(max)

NULL

PumlIndexColumnList

PumlIndexColumnList

nvarchar(max)

NULL

referenced_index_guid

referenced_index_guid

uniqueidentifier

NULL

RepoObject_fullname

RepoObject_fullname

nvarchar(261)

NOT NULL

Description

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


RowNumber_PatternPerParentObject

RowNumber_PatternPerParentObject

bigint

NULL

RowNumber_PkPerParentObject

RowNumber_PkPerParentObject

bigint

NULL

SysObject_fullname

SysObject_fullname

nvarchar(261)

NOT NULL

Description

(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))


SysObject_fullname2

SysObject_fullname2

nvarchar(257)

NOT NULL

Description

(concat([SysObject_schema_name],'.',[SysObject_name]))


SysObject_id

SysObject_id

int

NULL

SysObject_type

SysObject_type

char(2)

NULL

sql_modules_definition

repo.Index_gross - V script
CREATE View repo.Index_gross
As
--
Select
    T1.index_guid
  , T2.index_name
  , T2.index_type
  , T1.IndexPatternColumnDatatype
  , T1.IndexPatternColumnName
  , T1.IndexSemanticGroup
  , T2.is_index_disabled
  , T2.is_index_primary_key
  , T2.is_index_real
  , T2.is_index_unique
  , T3.is_persistence
  , T3.is_repo_managed
  , T3.is_DocsExclude
  , T3.is_DocsOutput
  , T3.is_external
  , T2.parent_RepoObject_guid
  , T2.referenced_index_guid
  , T3.RepoObject_fullname
  , T3.RepoObject_fullname2
  --if [RowNumber_PatternPerParentObject] > 1 then these are duplicates by same ColumnPattern and normally should be deleted, at least in [repo].[Index_virtual]
  , RowNumber_PatternPerParentObject =
  --
  Row_Number () Over ( Partition By
                           T2.parent_RepoObject_guid
                         , T1.IndexPatternColumnName
                       Order By
                           --priority has real index
                           T2.is_index_real Desc
                         --priority PK
                         , T2.is_index_primary_key Desc
                         --priority not disabled
                         , T2.is_index_disabled
                         --priority first added index
                         , T2.index_guid
                     )
  , RowNumber_PkPerParentObject      =
  --
  Row_Number () Over ( Partition By
                           T2.parent_RepoObject_guid
                         , T2.is_index_primary_key
                       Order By
                           --priority has real index
                           T2.is_index_real Desc
                         --priority not disabled
                         , T2.is_index_disabled
                         --priority first added index
                         , T2.index_guid
                     )
  , T3.SysObject_fullname
  , T3.SysObject_fullname2
  , T3.SysObject_schema_name
  , T3.SysObject_name
  , T3.SysObject_type
  , T3.SysObject_id
  , ColumList.AntoraIndexColumnList
  , ColumList.ColumnList
  , ColumList.ConstraintColumnList
  , ColumList.DbmlIndexColumnList
  , ColumList.PersistenceWhereColumnList
  , ColumList.PumlIndexColumnList
From
    repo.Index_Settings        As T1
    Inner Join
        repo.Index_union       As T2
            On
            T2.index_guid        = T1.index_guid

    Inner Join
        repo.RepoObject_gross  As T3
            On
            T3.RepoObject_guid   = T2.parent_RepoObject_guid

    Left Join
        repo.Index_ColumList_T As ColumList
            On
            ColumList.index_guid = T1.index_guid