repo.RepoObject_gross - V
type: V ( view ), modify_date: 2022-01-13 19:32:38
RepoObject_guid: 7790291C-9D61-EB11-84DC-A81E8446D5B0
Columns
| PK | Column Name | Data Type | NULL? | ID |
|---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
References
Referencing Objects
Indexes
idx_RepoObject_gross__1
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross__2
-
IndexSemanticGroup: no_group
-
[column-usp_persistence_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross__3
-
IndexSemanticGroup: no_group
-
[column-SysObject_schema_name]; nvarchar(128)
-
[column-SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross__4
-
IndexSemanticGroup: no_group
-
[column-RepoObject_schema_name]; nvarchar(128)
-
[column-RepoObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross__5
-
IndexSemanticGroup: no_group
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross__6
-
IndexSemanticGroup: no_group
-
[column-pk_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_gross__7
-
IndexSemanticGroup: no_group
-
[column-AntoraComponent]; nvarchar(max)
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
external_RepoDatabaseName
external_RepoDatabaseName |
|
|
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))
indent_sql_modules_definition
indent_sql_modules_definition |
|
|
Inheritance_StringAggSeparatorSql
Inheritance_StringAggSeparatorSql |
|
|
InheritanceDefinition
InheritanceDefinition |
|
|
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_persistence_delete_changed
is_persistence_delete_changed |
|
|
is_persistence_delete_missing
is_persistence_delete_missing |
|
|
is_persistence_persist_source
is_persistence_persist_source |
|
|
is_persistence_update_changed
is_persistence_update_changed |
|
|
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_RepoObject_xref
persistence_source_RepoObject_xref |
|
|
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]))
persistence_source_SysObject_xref
persistence_source_SysObject_xref |
|
|
pk_IndexPatternColumnDatatype
pk_IndexPatternColumnDatatype |
|
|
pk_IndexPatternColumnName
pk_IndexPatternColumnName |
|
|
pk_IndexPatternColumnName_new
pk_IndexPatternColumnName_new |
|
|
pk_IndexSemanticGroup
pk_IndexSemanticGroup |
|
|
Repo_history_table_guid
Repo_history_table_guid |
|
|
RepoObject_fullname
RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
RepoObject_fullname2
RepoObject_fullname2 |
|
|
(concat([RepoObject_schema_name],'.',[RepoObject_name]))
RepoObject_guid
RepoObject_guid |
|
|
RepoObject_Referencing_Count
RepoObject_Referencing_Count |
|
|
SysObject_fullname
SysObject_fullname |
|
|
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
SysObject_fullname2
SysObject_fullname2 |
|
|
(concat([SysObject_schema_name],'.',[SysObject_name]))
SysObject_parent_object_id
SysObject_parent_object_id |
|
|
SysObject_query_executed_dt
SysObject_query_executed_dt |
|
|
SysObject_query_sql
SysObject_query_sql |
|
|
(concat('SELECT * FROM [',[config].[fs_dwh_database_name](),'].[',[SysObject_schema_name],'].[',[SysObject_name],']'))
SysObject_schema_name
SysObject_schema_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])
usp_persistence_RepoObject_guid
usp_persistence_RepoObject_guid |
|
|
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