repo.RepoObject_gross - V

type: V ( view ), modify_date: 2022-01-13 19:32:38

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

Description

Examples

Entity Diagram

entity-repo.repoobject_gross

Columns

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

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

char(1)

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

tinyint

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

tinyint

NULL

bit

NOT NULL

int

NULL

bit

NOT NULL

int

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(4000)

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

nvarchar(512)

NULL

nvarchar(max)

NULL

nvarchar(max)

NULL

nvarchar(4000)

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

datetime

NULL

xml

NULL

nvarchar(406)

NOT NULL

nvarchar(128)

NOT NULL

char(2)

NULL

nvarchar(128)

NULL

nvarchar(500)

NULL

nvarchar(max)

NULL

bit

NULL

tinyint

NULL

nvarchar(140)

NOT NULL

uniqueidentifier

NULL

int

NULL

Foreign Key Diagram

entity_1_1_fk-repo.repoobject_gross

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.repoobject_gross

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.repoobject_gross

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.repoobject_gross

Column Reference Diagram

entity_1_1_colref-repo.repoobject_gross

Indexes

idx_RepoObject_gross__1

idx_RepoObject_gross__2

idx_RepoObject_gross__3

idx_RepoObject_gross__4

idx_RepoObject_gross__5

idx_RepoObject_gross__6

idx_RepoObject_gross__7

Column Details

_

AntoraComponent

AntoraComponent

nvarchar(max)

NULL

AntoraModule

AntoraModule

nvarchar(max)

NULL

Description

Description

nvarchar(max)

NULL

ExecutionLogId_action

ExecutionLogId_action

char(1)

NULL

external_AntoraComponent

external_AntoraComponent

nvarchar(128)

NULL

external_AntoraModule

external_AntoraModule

nvarchar(128)

NULL

external_DatabaseName

external_DatabaseName

nvarchar(128)

NULL

external_RepoDatabaseName

external_RepoDatabaseName

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

indent_sql_modules_definition

indent_sql_modules_definition

tinyint

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_in_reference

is_in_reference

int

NOT 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_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
Referencing Columns

node_id

node_id

bigint

NULL

Description

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


Referenced Columns
Referencing 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

pk_IndexPatternColumnDatatype

pk_IndexPatternColumnDatatype

nvarchar(4000)

NULL

pk_IndexPatternColumnName

pk_IndexPatternColumnName

nvarchar(4000)

NULL

pk_IndexPatternColumnName_new

pk_IndexPatternColumnName_new

nvarchar(4000)

NULL

pk_IndexSemanticGroup

pk_IndexSemanticGroup

nvarchar(512)

NULL

postscript

postscript

nvarchar(max)

NULL

Referencing Columns

prescript

prescript

nvarchar(max)

NULL

Referencing Columns

Property_ms_description

Property_ms_description

nvarchar(4000)

NULL

Repo_history_table_guid

Repo_history_table_guid

uniqueidentifier

NULL

Repo_temporal_type

Repo_temporal_type

tinyint

NULL

RepoObject_fullname2

RepoObject_fullname2

nvarchar(257)

NOT NULL

Description

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


RepoObject_name

RepoObject_name

nvarchar(128)

NOT NULL

Referenced Columns

RepoObject_Referencing_Count

RepoObject_Referencing_Count

int

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_executed_dt

SysObject_query_executed_dt

datetime

NULL

SysObject_query_plan

SysObject_query_plan

xml

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_name

SysObject_type_name

nvarchar(128)

NULL

Referenced Columns

tables_dataCategory

tables_dataCategory

nvarchar(500)

NULL

tables_description

tables_description

nvarchar(max)

NULL

tables_isHidden

tables_isHidden

bit

NULL

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])


uspgenerator_usp_id

uspgenerator_usp_id

int

NULL

Referenced Columns

sql_modules_definition

repo.RepoObject_gross - V script
CREATE View repo.RepoObject_gross
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.indent_sql_modules_definition
  , 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.ExecutionLogId_action
  , ro_p.history_schema_name
  , ro_p.history_table_name
  , 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 )

--, ssas_Description                        = ssastab.Description
--, ssas_IsHidden                           = ssastab.IsHidden
--, ssas_IsPrivate                          = ssastab.IsPrivate
--, ssas_ShowAsVariationsOnly               = ssastab.ShowAsVariationsOnly
--, ColumnList.CreateColumnList
--, ColumnList.DbmlColumnList
--, ColumnList.PersistenceCompareColumnList
--, ColumnList.PersistenceInsertColumnList
--, ColumnList.PersistenceUpdateColumnList
--, SqlModules.sql_modules_definition
--, sql_modules_antora                      = Replace (
--                                                        Replace (
--                                                                    Replace ( SqlModules.sql_modules_definition, '\include::', '\\include::' )
--                                                                  , '\ifdef::'
--                                                                  , '\\ifdef::'
--                                                                )
--                                                      , '\endif::'
--                                                      , '\\endif::'
--                                                    )
--, SqlModules.sql_modules_formatted
--, SqlModules.sql_modules_formatted2
--, ro_referenced.AntoraReferencedList
--, ro_referencing.AntoraReferencingList
--, ext_referenced.AntoraExternalReferencedList
--, ext_referencing.AntoraExternalReferencingList
--, AntoraModule                             = AntoraModule.Parameter_value_result
--, AntoraComponent                         = AntoraComponent.Parameter_value_result
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
--Left Outer Join
--    ssas.TMSCHEMA_TABLES_T                          As ssastab
--        On
--        ssastab.RepoObject_guid             = ro.RepoObject_guid

--Left Outer Join
--    repo.RepoObject_ColumnList                                      As ColumnList
--        On
--        ColumnList.RepoObject_guid = ro.RepoObject_guid

--Left Outer Join
--    sqlparse.RepoObject_SqlModules_Repo_Sys                         As SqlModules
--        On
--        SqlModules.RepoObject_guid = ro.RepoObject_guid

--Left Join
--    reference.RepoObject_ReferencedList                             As ro_referenced
--        On
--        ro_referenced.Referencing_guid = ro.RepoObject_guid

--Left Join
--    reference.RepoObject_ReferencingList                            As ro_referencing
--        On
--        ro_referencing.Referenced_guid = ro.RepoObject_guid

--Left Join
--    reference.RepoObject_ExternalReferencedList  As ext_referenced
--        On
--        ext_referenced.RepoObject_guid      = ro.RepoObject_guid

--Left Join
--    reference.RepoObject_ExternalReferencingList As ext_referencing
--        On
--        ext_referencing.RepoObject_guid     = ro.RepoObject_guid

--Cross Join config.ftv_get_parameter_value ( 'AntoraComponent', '' ) As AntoraComponent
--Cross Join config.ftv_get_parameter_value ( 'AntoraModule', '' ) As AntoraModule