repo.RepoObject_gross_persistence - V
type: V ( view ), modify_date: 2022-01-13 19:31:58
RepoObject_guid: 89F74AD3-5048-EC11-8530-A81E8446D5B0
Description
to avoid cyclic object references we use a limited repo.RepoObject_gross_persistence - V instead of repo.RepoObject_gross - V in reference.RepoObject_reference_persistence_target_as_source - V
Columns
| PK | Column Name | Data Type | NULL? | ID |
|---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_RepoObject_gross_persistence__1
-
IndexSemanticGroup: no_group
-
[column-usp_persistence_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross_persistence__2
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross_persistence__3
-
IndexSemanticGroup: no_group
-
[column-SysObject_schema_name]; nvarchar(128)
-
[column-SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross_persistence__4
-
IndexSemanticGroup: no_group
-
[column-RepoObject_schema_name]; nvarchar(128)
-
[column-RepoObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross_persistence__5
-
IndexSemanticGroup: no_group
-
[column-pk_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
has_different_sys_names
has_different_sys_names |
|
|
(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))
Inheritance_StringAggSeparatorSql
Inheritance_StringAggSeparatorSql |
|
|
is_persistence_check_duplicate_per_pk
is_persistence_check_duplicate_per_pk |
|
|
is_persistence_check_for_empty_source
is_persistence_check_for_empty_source |
|
|
is_RepoObject_name_uniqueidentifier
is_RepoObject_name_uniqueidentifier |
|
|
(case when TRY_CAST([RepoObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
is_SysObject_name_uniqueidentifier
is_SysObject_name_uniqueidentifier |
|
|
(case when TRY_CAST([SysObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
node_id
node_id |
|
|
(CONVERT([bigint],[SysObject_id])*(10000))
persistence_source_RepoObject_fullname
persistence_source_RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
persistence_source_RepoObject_fullname2
persistence_source_RepoObject_fullname2 |
|
|
(concat([RepoObject_schema_name],'.',[RepoObject_name]))
persistence_source_RepoObject_guid
persistence_source_RepoObject_guid |
|
|
persistence_source_SysObject_fullname
persistence_source_SysObject_fullname |
|
|
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
persistence_source_SysObject_fullname_or_tempsource
persistence_source_SysObject_fullname_or_tempsource |
|
|
persistence_source_SysObject_fullname2
persistence_source_SysObject_fullname2 |
|
|
(concat([SysObject_schema_name],'.',[SysObject_name]))
RepoObject_fullname
RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
RepoObject_fullname2
RepoObject_fullname2 |
|
|
(concat([RepoObject_schema_name],'.',[RepoObject_name]))
SysObject_fullname
SysObject_fullname |
|
|
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
SysObject_fullname2
SysObject_fullname2 |
|
|
(concat([SysObject_schema_name],'.',[SysObject_name]))
SysObject_query_sql
SysObject_query_sql |
|
|
(concat('SELECT * FROM [',[config].[fs_dwh_database_name](),'].[',[SysObject_schema_name],'].[',[SysObject_name],']'))
temporal_type
temporal_type |
|
|
(CONVERT([tinyint],case [has_history] when (1) then (2) else (0) end))
usp_persistence_name
usp_persistence_name |
|
|
('usp_PERSIST_'+[RepoObject_name])
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