repo.RepoObjectColumn_gross - V
type: V ( view ), modify_date: 2022-09-05 16:51:04
RepoObject_guid: 6990291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_RepoObjectColumn_gross__1
-
IndexSemanticGroup: no_group
-
[column-SysObject_schema_name]; nvarchar(128)
-
[column-SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObjectColumn_gross__2
-
IndexSemanticGroup: no_group
-
[column-RepoObject_schema_name]; nvarchar(128)
-
[column-RepoObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObjectColumn_gross__3
-
IndexSemanticGroup: no_group
-
[column-RepoObjectColumn_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObjectColumn_gross__4
-
IndexSemanticGroup: no_group
-
[column-RepoObjectColumn_guid]; uniqueidentifier
-
[column-SysObjectColumn_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObjectColumn_gross__5
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
[column-RepoObjectColumn_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObjectColumn_gross__6
-
IndexSemanticGroup: no_group
-
[column-pk_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObjectColumn_gross__7
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
Column_name
Column_name |
|
|
(case when TRY_CAST([RepoObjectColumn_name] AS [uniqueidentifier]) IS NULL then [RepoObjectColumn_name] else [SysObjectColumn_name] end)
has_different_sys_names
has_different_sys_names |
|
|
(CONVERT([bit],case when [RepoObjectColumn_name]<>[SysObjectColumn_name] then (1) else (0) end))
Inheritance_StringAggSeparatorSql
Inheritance_StringAggSeparatorSql |
|
|
is_persistence_NoCompareButUpdate
is_persistence_NoCompareButUpdate |
|
|
is_persistence_NoCompareNoUpdate
is_persistence_NoCompareNoUpdate |
|
|
is_RepoObject_name_uniqueidentifier
is_RepoObject_name_uniqueidentifier |
|
|
(case when TRY_CAST([RepoObject_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
is_RepoObjectColumn_name_uniqueidentifier
is_RepoObjectColumn_name_uniqueidentifier |
|
|
(case when TRY_CAST([RepoObjectColumn_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)
is_SysObjectColumn_missing
is_SysObjectColumn_missing |
|
|
is_SysObjectColumn_name_uniqueidentifier
is_SysObjectColumn_name_uniqueidentifier |
|
|
(case when TRY_CAST([SysObjectColumn_name] AS [uniqueidentifier]) IS NULL then (0) else (1) end)
node_id
node_id |
|
|
(CONVERT([bigint],[SysObject_id])*(10000))
persistence_source_RepoObject_guid
persistence_source_RepoObject_guid |
|
|
persistence_source_RepoObjectColumn_guid
persistence_source_RepoObjectColumn_guid |
|
|
Repo_default_definition
Repo_default_definition |
|
|
Repo_default_is_system_named
Repo_default_is_system_named |
|
|
Repo_generated_always_type
Repo_generated_always_type |
|
|
Repo_uses_database_collation
Repo_uses_database_collation |
|
|
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 |
|
|
RepoObjectColumn_column_id
RepoObjectColumn_column_id |
|
|
RepoObjectColumn_guid
RepoObjectColumn_guid |
|
|
-
reference.RepoObjectColumn_reference_FirstResultSet.referencing_RepoObjectColumn_guid
-
reference.RepoObjectColumn_reference_FirstResultSet.referenced_RepoObjectColumn_guid
-
reference.RepoObjectColumn_reference_QueryPlan.referencing_RepoObjectColumn_guid
-
reference.RepoObjectColumn_reference_QueryPlan.referenced_RepoObjectColumn_guid
-
reference.RepoObjectColumn_reference_virtual.referencing_RepoObjectColumn_guid
-
reference.RepoObjectColumn_reference_virtual.referenced_RepoObjectColumn_guid
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 |
|
|
SysObjectColumn_column_id
SysObjectColumn_column_id |
|
|
-
reference.RepoObjectColumn_reference_FirstResultSet.referencing_minor_id
-
reference.RepoObjectColumn_reference_FirstResultSet.referenced_minor_id
-
reference.RepoObjectColumn_reference_QueryPlan.referencing_minor_id
-
reference.RepoObjectColumn_reference_QueryPlan.referenced_minor_id
-
reference.RepoObjectColumn_reference_virtual.referenced_minor_id
-
reference.RepoObjectColumn_reference_virtual.referencing_minor_id
sql_modules_definition
repo.RepoObjectColumn_gross - V script
CREATE View [repo].[RepoObjectColumn_gross]
As
Select
roc.RepoObjectColumn_guid
, ro.RepoObject_fullname
, RepoObjectColumn_fullname = Concat ( ro.RepoObject_fullname, '.', QuoteName ( roc.RepoObjectColumn_name ))
, roc.Column_name
, roc.has_different_sys_names
, roc.Inheritance_StringAggSeparatorSql
, roc.InheritanceDefinition
, roc.InheritanceType
, roc.is_persistence_Ignore
, roc.is_persistence_NoCompareButUpdate
, roc.is_persistence_NoCompareNoUpdate
, roc.is_persistence_NoInsert
, roc.is_query_plan_expression
, roc.is_RepoObjectColumn_name_uniqueidentifier
, roc.is_required_ColumnMerge
, roc.is_SysObjectColumn_missing
, roc.is_SysObjectColumn_name_uniqueidentifier
, persistence_source_RepoObject_guid = rop.source_RepoObject_guid
, roc.persistence_source_RepoObjectColumn_guid
, roc.Referencing_Count
, roc.Repo_default_definition
, roc.Repo_default_is_system_named
, roc.Repo_default_name
, roc.Repo_definition
, roc.Repo_generated_always_type
, roc.Repo_graph_type
, roc.Repo_is_computed
, roc.Repo_is_identity
, roc.Repo_is_nullable
, roc.Repo_is_persisted
, roc.Repo_seed_value
, roc.Repo_increment_value
, roc.Repo_user_type_fullname
, roc.Repo_user_type_name
, roc.Repo_uses_database_collation
, roc.RepoObject_guid
, roc.RepoObjectColumn_column_id
, RepoObjectColumn_fullname2 = Concat ( ro.RepoObject_fullname2, '.', roc.RepoObjectColumn_name )
, roc.RepoObjectColumn_name
, roc.SysObjectColumn_column_id
, roc.SysObjectColumn_name
, ro.has_get_referenced_issue
, ro.is_repo_managed
, ro.is_ssas
, ro.is_external
, ro.is_RepoObject_name_uniqueidentifier
, ro.is_SysObject_missing
, ro.is_SysObject_name_uniqueidentifier
, ro.modify_dt
, ro.node_id
, ro.pk_index_guid
, ro.RepoObject_fullname2
, ro.RepoObject_name
, ro.RepoObject_Referencing_Count
, ro.RepoObject_schema_name
, ro.RepoObject_type
, ro.SysObject_fullname
, ro.SysObject_fullname2
, ro.SysObject_id
, ro.SysObject_modify_date
, ro.SysObject_name
, ro.SysObject_schema_name
, ro.SysObject_type
, ro.SysObject_parent_object_id
--based on ro.pk_index_guid
--in other words: only, if the columns are part of the PK
, Property_ms_description = property.fs_get_RepoObjectColumnProperty_nvarchar (
roc.RepoObjectColumn_guid
, 'ms_description'
)
--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 (
--keep existing Description
property.fs_get_RepoObjectColumnProperty_nvarchar (
roc.RepoObjectColumn_guid
, 'Description'
)
, tabcol.tables_columns_description
, tabcol2.descriptions_StrAgg
, property.fs_get_RepoObjectColumnProperty_nvarchar (
roc.RepoObjectColumn_guid
, 'ms_description'
)
)
, tabcol_Description = Coalesce ( tabcol.tables_columns_description, tabcol2.descriptions_StrAgg )
, tabcol_DisplayFolder = tabcol.tables_columns_displayFolder
, tabcol_Expression = Coalesce ( tabcol.tables_columns_expression, tabcol3.expressions_StrAgg )
, tabcol_FormatString = tabcol.tables_columns_formatString
--required in String_Agg in next steps
, tabcol_IsHidden = IsNull ( tabcol.tables_columns_isHidden, 0 )
, tabcol_IsKey = IsNull ( tabcol.tables_columns_isKey, 0 )
, tabcol_IsUnique = IsNull ( tabcol.tables_columns_isUnique, 0 )
, tabcol_SummarizeBy = tabcol.tables_columns_summarizeBy
, tabcol_Type = tabcol.tables_columns_type
--, ic.index_column_id
--, ic.index_name
--, ic.is_index_primary_key
--, isAnyIndexColumn =
-- (
-- Select
-- Top 1
-- 1
-- From
-- repo.IndexColumn_union As icu
-- Where
-- icu.RepoObjectColumn_guid = roc.RepoObjectColumn_guid
-- )
--, roc_referenced.AntoraReferencedColumnList
--, roc_referencing.AntoraReferencingColumnList
From
repo.RepoObjectColumn As roc
Inner Join
repo.RepoObject As ro
On
roc.RepoObject_guid = ro.RepoObject_guid
Left Join
repo.RepoObject_persistence As rop
On
roc.RepoObject_guid = rop.target_RepoObject_guid
Left Join
ssas.model_json_311_tables_columns_T As tabcol
On
tabcol.RepoObjectColumn_guid = roc.RepoObjectColumn_guid
Left Join
ssas.model_json_31111_tables_columns_descriptions_StrAgg As tabcol2
On
tabcol2.RepoObjectColumn_guid = roc.RepoObjectColumn_guid
Left Join
ssas.model_json_31121_tables_columns_expressions_StrAgg As tabcol3
On
tabcol3.RepoObjectColumn_guid = roc.RepoObjectColumn_guid
--Left Join
-- ssas.TMSCHEMA_COLUMNS_T As ssascol
-- On
-- ssascol.RepoObjectColumn_guid = roc.RepoObjectColumn_guid
--Left Outer Join
-- repo.IndexColumn_union As ic
-- On
-- ic.index_guid = ro.pk_index_guid
-- And ic.RepoObjectColumn_guid = roc.RepoObjectColumn_guid
--Left Join
-- reference.RepoObjectColumn_ReferencedList As roc_referenced
-- On
-- roc_referenced.Referencing_guid = roc.RepoObjectColumn_guid
--Left Join
-- reference.RepoObjectColumn_ReferencingList As roc_referencing
-- On
-- roc_referencing.Referenced_guid = roc.RepoObjectColumn_guid