repo.RepoObject_gross_persistence - V

type: V ( view ), modify_date: 2022-01-13 19:31:58

RepoObject_guid: 89F74AD3-5048-EC11-8530-A81E8446D5B0

Examples

Entity Diagram

entity-repo.repoobject_gross_persistence

Columns

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

char(1)

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

tinyint

NULL

bit

NOT NULL

int

NULL

bit

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

int

NOT NULL

bit

NULL

bit

NOT NULL

bit

NULL

int

NOT NULL

datetime

NOT NULL

bigint

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

uniqueidentifier

NULL

nvarchar(4000)

NULL

nvarchar(261)

NULL

nvarchar(261)

NULL

nvarchar(257)

NULL

nvarchar(4000)

NULL

uniqueidentifier

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

uniqueidentifier

NULL

tinyint

NULL

nvarchar(261)

NOT NULL

nvarchar(257)

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

int

NULL

nvarchar(128)

NOT NULL

char(2)

NOT NULL

nvarchar(128)

NULL

nvarchar(261)

NOT NULL

nvarchar(257)

NOT NULL

int

NULL

datetime

NULL

nvarchar(128)

NOT NULL

int

NOT NULL

nvarchar(406)

NOT NULL

nvarchar(128)

NOT NULL

char(2)

NULL

nvarchar(128)

NULL

tinyint

NULL

nvarchar(140)

NOT NULL

uniqueidentifier

NULL

Foreign Key Diagram

entity_1_1_fk-repo.repoobject_gross_persistence

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.repoobject_gross_persistence

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.repoobject_gross_persistence

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.repoobject_gross_persistence

Column Reference Diagram

entity_1_1_colref-repo.repoobject_gross_persistence

Indexes

idx_RepoObject_gross_persistence__1

idx_RepoObject_gross_persistence__2

idx_RepoObject_gross_persistence__3

idx_RepoObject_gross_persistence__4

idx_RepoObject_gross_persistence__5

Column Details

_

ExecutionLogId_action

ExecutionLogId_action

char(1)

NULL

external_AntoraComponent

external_AntoraComponent

nvarchar(128)

NULL

external_AntoraModule

external_AntoraModule

nvarchar(128)

NULL

has_different_sys_names

has_different_sys_names

bit

NULL

Description

(CONVERT([bit],case when [RepoObject_schema_name]<>[SysObject_schema_name] OR [RepoObject_name]<>[SysObject_name] OR [RepoObject_type]<>[SysObject_type] then (1) else (0) end))


has_execution_plan_issue

has_execution_plan_issue

bit

NULL

has_get_referenced_issue

has_get_referenced_issue

bit

NULL

has_history

has_history

bit

NULL

has_history_columns

has_history_columns

bit

NULL

history_schema_name

history_schema_name

nvarchar(128)

NULL

history_table_name

history_table_name

nvarchar(128)

NULL

Inheritance_StringAggSeparatorSql

Inheritance_StringAggSeparatorSql

nvarchar(4000)

NULL

InheritanceDefinition

InheritanceDefinition

nvarchar(4000)

NULL

InheritanceType

InheritanceType

tinyint

NULL

Referenced Columns

is_DocsExclude

is_DocsExclude

bit

NOT NULL

Referenced Columns

is_DocsOutput

is_DocsOutput

int

NULL

Referenced Columns

is_external

is_external

bit

NOT NULL

Referenced Columns

is_persistence

is_persistence

bit

NULL

is_persistence_check_duplicate_per_pk

is_persistence_check_duplicate_per_pk

bit

NULL

is_persistence_check_for_empty_source

is_persistence_check_for_empty_source

bit

NULL

is_persistence_delete_changed

is_persistence_delete_changed

bit

NULL

is_persistence_delete_missing

is_persistence_delete_missing

bit

NULL

is_persistence_insert

is_persistence_insert

bit

NULL

is_persistence_persist_source

is_persistence_persist_source

bit

NULL

is_persistence_truncate

is_persistence_truncate

bit

NULL

is_persistence_update_changed

is_persistence_update_changed

bit

NULL

is_repo_managed

is_repo_managed

bit

NULL

Referenced Columns

is_RepoObject_name_uniqueidentifier

is_RepoObject_name_uniqueidentifier

int

NOT NULL

Description

(case when TRY_CAST([RepoObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)


is_required_ObjectMerge

is_required_ObjectMerge

bit

NULL

is_ssas

is_ssas

bit

NOT NULL

Referenced Columns

is_SysObject_missing

is_SysObject_missing

bit

NULL

is_SysObject_name_uniqueidentifier

is_SysObject_name_uniqueidentifier

int

NOT NULL

Description

(case when TRY_CAST([SysObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)


modify_dt

modify_dt

datetime

NOT NULL

Referenced Columns

node_id

node_id

bigint

NULL

Description

(CONVERT([bigint],[SysObject_id])*(10000))


Referenced Columns

persistence_source_RepoObject_fullname

persistence_source_RepoObject_fullname

nvarchar(261)

NULL

Description

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


persistence_source_RepoObject_fullname2

persistence_source_RepoObject_fullname2

nvarchar(257)

NULL

Description

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


persistence_source_RepoObject_guid

persistence_source_RepoObject_guid

uniqueidentifier

NULL

persistence_source_RepoObject_xref

persistence_source_RepoObject_xref

nvarchar(4000)

NULL

persistence_source_SysObject_fullname

persistence_source_SysObject_fullname

nvarchar(261)

NULL

Description

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


persistence_source_SysObject_fullname_or_tempsource

persistence_source_SysObject_fullname_or_tempsource

nvarchar(261)

NULL

persistence_source_SysObject_fullname2

persistence_source_SysObject_fullname2

nvarchar(257)

NULL

Description

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


persistence_source_SysObject_xref

persistence_source_SysObject_xref

nvarchar(4000)

NULL

pk_index_guid

pk_index_guid

uniqueidentifier

NULL

Referenced Columns

postscript

postscript

nvarchar(max)

NULL

prescript

prescript

nvarchar(max)

NULL

Repo_history_table_guid

Repo_history_table_guid

uniqueidentifier

NULL

Repo_temporal_type

Repo_temporal_type

tinyint

NULL

RepoObject_fullname

RepoObject_fullname

nvarchar(261)

NOT NULL

Description

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


RepoObject_fullname2

RepoObject_fullname2

nvarchar(257)

NOT NULL

Description

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


RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

Referenced Columns

RepoObject_name

RepoObject_name

nvarchar(128)

NOT NULL

Referenced Columns

RepoObject_Referencing_Count

RepoObject_Referencing_Count

int

NULL

RepoObject_schema_name

RepoObject_schema_name

nvarchar(128)

NOT NULL

RepoObject_type

RepoObject_type

char(2)

NOT NULL

Referenced Columns

RepoObject_type_name

RepoObject_type_name

nvarchar(128)

NULL

Referenced Columns

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

Referenced Columns

SysObject_modify_date

SysObject_modify_date

datetime

NULL

SysObject_name

SysObject_name

nvarchar(128)

NOT NULL

Referenced Columns

SysObject_parent_object_id

SysObject_parent_object_id

int

NOT NULL

SysObject_query_sql

SysObject_query_sql

nvarchar(406)

NOT NULL

Description

(concat('SELECT * FROM [',[config].[fs_dwh_database_name](),'].[',[SysObject_schema_name],'].[',[SysObject_name],']'))


SysObject_schema_name

SysObject_schema_name

nvarchar(128)

NOT NULL

SysObject_type

SysObject_type

char(2)

NULL

Referenced Columns

SysObject_type_name

SysObject_type_name

nvarchar(128)

NULL

Referenced Columns

temporal_type

temporal_type

tinyint

NULL

Description

(CONVERT([tinyint],case [has_history] when (1) then (2) else (0) end))


usp_persistence_name

usp_persistence_name

nvarchar(140)

NOT NULL

Description

('usp_PERSIST_'+[RepoObject_name])


usp_persistence_RepoObject_guid

usp_persistence_RepoObject_guid

uniqueidentifier

NULL

Referenced Columns

sql_modules_definition

repo.RepoObject_gross_persistence - V script
/*
<<property_start>>Description
to avoid cyclic object references we use a limited xref:sqldb:repo.repoobject_gross_persistence.adoc[] instead of xref:sqldb:repo.repoobject_gross.adoc[] in xref:sqldb:reference.repoobject_reference_persistence_target_as_source.adoc[]
<<property_end>>
*/
CREATE View repo.RepoObject_gross_persistence
As
Select
    --
    ro.RepoObject_guid
  , ro.RepoObject_schema_name
  , ro.RepoObject_name
  , ro.RepoObject_type
  , RepoObject_type_name                                = repo_type.type_desc
  , ro.has_different_sys_names
  , ro.has_execution_plan_issue
  , ro.has_get_referenced_issue
  , ro.Inheritance_StringAggSeparatorSql
  , ro.InheritanceDefinition
  , ro.InheritanceType
  , ro.is_DocsExclude
  , ty.is_DocsOutput
  --, is_in_reference                                     = Case
  --                                                            When Exists
  --                                                                 (
  --                                                                     Select
  --                                                                         1
  --                                                                     From
  --                                                                         reference.RepoObject_ReferencedReferencing As ref
  --                                                                     Where
  --                                                                         ref.Referenced_guid     = ro.RepoObject_guid
  --                                                                         Or ref.Referencing_guid = ro.RepoObject_guid
  --                                                                 )
  --                                                                Then
  --                                                                1
  --                                                            Else
  --                                                                0
  --                                                        End
  , ro.is_repo_managed
  , ro.is_ssas
  , ro.is_required_ObjectMerge
  , ro.is_RepoObject_name_uniqueidentifier
  , ro.is_SysObject_missing
  , ro.is_SysObject_name_uniqueidentifier
  , ro.modify_dt
  , ro.node_id
  , ro.pk_index_guid
  --, pk_IndexPatternColumnDatatype                       = ipk.IndexPatternColumnDatatype
  --, pk_IndexPatternColumnName                           = ipk.IndexPatternColumnName
  --, ro.pk_IndexPatternColumnName_new
  --, pk_IndexSemanticGroup                               = ipk.IndexSemanticGroup
  , ro.Repo_history_table_guid
  , ro.Repo_temporal_type
  , ro.RepoObject_fullname
  , ro.RepoObject_fullname2
  , ro.RepoObject_Referencing_Count
  , ro.SysObject_fullname
  , ro.SysObject_fullname2
  , ro.SysObject_id
  , ro.SysObject_modify_date
  , ro.SysObject_name
  , ro.SysObject_parent_object_id
  --, QueryPlan.SysObject_query_executed_dt
  --, QueryPlan.SysObject_query_plan
  , ro.SysObject_query_sql
  , ro.SysObject_schema_name
  , ro.SysObject_type
  , ro.external_AntoraComponent
  , ro.external_AntoraModule
  --, external_DatabaseName                               = ard.DatabaseName
  --, external_RepoDatabaseName                           = ard.RepoDatabaseName
  , ro.is_external
  --, AntoraComponent                                     = Coalesce ( ro.external_AntoraComponent, AntoraComponent.Parameter_value_result )
  --, AntoraModule                                        = Coalesce ( ro.external_AntoraModule, AntoraModule.Parameter_value_result )
  , SysObject_type_name                                 = sys_type.type_desc
  , ro.usp_persistence_name
  , usp_persistence_RepoObject_guid                     = ro_usp_p.RepoObject_guid
  , persistence_source_RepoObject_guid                  = ro_p.source_RepoObject_guid
  , persistence_source_RepoObject_fullname              = ro_p_s.RepoObject_fullname
  , persistence_source_RepoObject_fullname2             = ro_p_s.RepoObject_fullname2
  , persistence_source_RepoObject_xref                  = 'xref:' + docs.fs_cleanStringForFilename ( ro_p_s.RepoObject_fullname2 )
                                                          + '.adoc[]'
  , persistence_source_SysObject_fullname               = ro_p_s.SysObject_fullname
  , persistence_source_SysObject_fullname_or_tempsource = Iif(ro_p.is_persistence_persist_source = 1
                                                              , '#source'
                                                              , ro_p_s.SysObject_fullname)
  , persistence_source_SysObject_fullname2              = ro_p_s.SysObject_fullname2
  , persistence_source_SysObject_xref                   = 'xref:' + docs.fs_cleanStringForFilename ( ro_p_s.SysObject_fullname2 )
                                                          + '.adoc[]'
  --, uspgenerator_usp_id                                 = gusp.id
  , ro_p.has_history
  , ro_p.has_history_columns
  , ro_p.is_persistence
  , ro_p.is_persistence_check_duplicate_per_pk
  , ro_p.is_persistence_check_for_empty_source
  , ro_p.is_persistence_delete_missing
  , ro_p.is_persistence_delete_changed
  , ro_p.is_persistence_insert
  , ro_p.is_persistence_truncate
  , ro_p.is_persistence_update_changed
  --, ro_p.is_persistence_merge_delete_missing
  --, ro_p.is_persistence_merge_insert
  --, ro_p.is_persistence_merge_update_changed
  , ro_p.is_persistence_persist_source
  , ro_p.history_schema_name
  , ro_p.history_table_name
  , ro_p.ExecutionLogId_action
  , prescript                                           = ro_p.prescript
  , postscript                                          = ro_p.postscript
  , ro_p.temporal_type
--Attention, this will be written back into Property 'Description'
--this could be an issue, if it will be changed in differen places, which should be the primary?
--, Description                                         = Coalesce (
--                                                                     --use description in uspgenerator.GeneratorUsp
--                                                                     NullIf(gusp.usp_Description, '')
--                                                                   --keep existing Description
--                                                                   , NullIf(property.fs_get_RepoObjectProperty_nvarchar ( ro.RepoObject_guid, 'Description' ), '')
--                                                                   , modeltab.tables_description
--                                                                   , modeltab2.descriptions_StrAgg
--                                                                   , property.fs_get_RepoObjectProperty_nvarchar ( ro.RepoObject_guid, 'ms_description' )
--                                                                 )
--, Property_ms_description                             = property.fs_get_RepoObjectProperty_nvarchar ( ro.RepoObject_guid, 'ms_description' )
--, modeltab.tables_dataCategory
--, modeltab.tables_isHidden
--, tables_description                                  = Coalesce ( modeltab.tables_description, modeltab2.descriptions_StrAgg )
From
    repo.RepoObject                 As ro
    Left Outer Join
        repo.RepoObject_persistence As ro_p
            On
            ro_p.target_RepoObject_guid         = ro.RepoObject_guid

    Left Outer Join
        repo.RepoObject             As ro_p_s
            On
            ro_p_s.RepoObject_guid              = ro_p.source_RepoObject_guid

    Left Outer Join
        repo.RepoObject             As ro_usp_p
            On
            ro_usp_p.RepoObject_name            = ro.usp_persistence_name
            And ro_usp_p.RepoObject_schema_name = ro.RepoObject_schema_name

    --Left Outer Join
    --    reference.RepoObject_QueryPlan                                  As QueryPlan
    --        On
    --        QueryPlan.RepoObject_guid = ro.RepoObject_guid

    --Left Join
    --    repo.Index_Settings                                             As ipk
    --        On
    --        ipk.index_guid = ro.pk_index_guid

    Left Join
        configT.type                As repo_type
            On
            repo_type.type                      = ro.RepoObject_type

    Left Join
        configT.type                As sys_type
            On
            sys_type.type                       = ro.SysObject_type

    Left Join
        configT.type                As ty
            On
            ty.type                             = ro.RepoObject_type

--Left Outer Join
--    ssas.model_json_31_tables_T                                     As modeltab
--        On
--        modeltab.RepoObject_guid = ro.RepoObject_guid

--Left Outer Join
--    ssas.model_json_3161_tables_descriptions_StrAgg                 As modeltab2
--        On
--        modeltab2.RepoObject_guid = ro.RepoObject_guid

--Left Join
--    uspgenerator.GeneratorUsp                                       As gusp
--        On
--        gusp.usp_fullname = ro.RepoObject_fullname

--Left Join
--    reference.additional_Reference_database_T                       As ard
--        On
--        ard.AntoraComponent = ro.external_AntoraComponent
--        And ard.AntoraModule = ro.external_AntoraModule
--Cross Join config.ftv_get_parameter_value ( 'AntoraComponent', '' ) As AntoraComponent
--Cross Join config.ftv_get_parameter_value ( 'AntoraModule', '' ) As AntoraModule