repo.RepoObjectColumn_gross - V

type: V ( view ), modify_date: 2022-09-05 16:51:04

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

Description

Examples

Entity Diagram

entity-repo.repoobjectcolumn_gross

Columns

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

nvarchar(128)

NOT NULL

nvarchar(max)

NULL

bit

NULL

bit

NULL

nvarchar(4000)

NULL

nvarchar(4000)

NULL

tinyint

NULL

bit

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

int

NOT NULL

int

NOT NULL

bit

NULL

bit

NOT NULL

bit

NULL

int

NOT NULL

bit

NULL

int

NOT NULL

datetime

NOT NULL

bigint

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(4000)

NULL

int

NULL

nvarchar(max)

NULL

bit

NULL

nvarchar(128)

NULL

nvarchar(max)

NULL

tinyint

NOT NULL

int

NULL

sql_variant

NULL

bit

NOT NULL

bit

NOT NULL

bit

NULL

bit

NULL

sql_variant

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

bit

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

int

NULL

nvarchar(520)

NOT NULL

nvarchar(386)

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(261)

NOT NULL

nvarchar(257)

NOT NULL

int

NULL

datetime

NULL

nvarchar(128)

NOT NULL

int

NOT NULL

nvarchar(128)

NOT NULL

char(2)

NULL

int

NULL

nvarchar(128)

NOT NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

nvarchar(max)

NULL

nvarchar(500)

NULL

bit

NOT NULL

bit

NOT NULL

bit

NOT NULL

nvarchar(500)

NULL

nvarchar(500)

NULL

Foreign Key Diagram

entity_1_1_fk-repo.repoobjectcolumn_gross

Indexes

idx_RepoObjectColumn_gross__1

idx_RepoObjectColumn_gross__2

idx_RepoObjectColumn_gross__3

idx_RepoObjectColumn_gross__4

idx_RepoObjectColumn_gross__5

idx_RepoObjectColumn_gross__6

idx_RepoObjectColumn_gross__7

Column Details

_

Column_name

Column_name

nvarchar(128)

NOT NULL

Description

(case when TRY_CAST([RepoObjectColumn_name] AS [uniqueidentifier]) IS NULL then [RepoObjectColumn_name] else [SysObjectColumn_name] end)


Description

Description

nvarchar(max)

NULL

has_different_sys_names

has_different_sys_names

bit

NULL

Description

(CONVERT([bit],case when [RepoObjectColumn_name]<>[SysObjectColumn_name] then (1) else (0) end))


has_get_referenced_issue

has_get_referenced_issue

bit

NULL

Inheritance_StringAggSeparatorSql

Inheritance_StringAggSeparatorSql

nvarchar(4000)

NULL

InheritanceDefinition

InheritanceDefinition

nvarchar(4000)

NULL

is_external

is_external

bit

NOT NULL

Referenced Columns

is_persistence_Ignore

is_persistence_Ignore

bit

NULL

is_persistence_NoCompareButUpdate

is_persistence_NoCompareButUpdate

bit

NULL

is_persistence_NoCompareNoUpdate

is_persistence_NoCompareNoUpdate

bit

NULL

is_persistence_NoInsert

is_persistence_NoInsert

bit

NULL

is_query_plan_expression

is_query_plan_expression

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_RepoObjectColumn_name_uniqueidentifier

is_RepoObjectColumn_name_uniqueidentifier

int

NOT NULL

Description

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


is_required_ColumnMerge

is_required_ColumnMerge

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)


is_SysObjectColumn_missing

is_SysObjectColumn_missing

bit

NULL

is_SysObjectColumn_name_uniqueidentifier

is_SysObjectColumn_name_uniqueidentifier

int

NOT NULL

Description

(case when TRY_CAST([SysObjectColumn_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_guid

persistence_source_RepoObject_guid

uniqueidentifier

NULL

persistence_source_RepoObjectColumn_guid

persistence_source_RepoObjectColumn_guid

uniqueidentifier

NULL

pk_index_guid

pk_index_guid

uniqueidentifier

NULL

Referenced Columns

Property_ms_description

Property_ms_description

nvarchar(4000)

NULL

Referencing_Count

Referencing_Count

int

NULL

Repo_default_definition

Repo_default_definition

nvarchar(max)

NULL

Repo_default_is_system_named

Repo_default_is_system_named

bit

NULL

Repo_default_name

Repo_default_name

nvarchar(128)

NULL

Repo_definition

Repo_definition

nvarchar(max)

NULL

Repo_generated_always_type

Repo_generated_always_type

tinyint

NOT NULL

Repo_graph_type

Repo_graph_type

int

NULL

Repo_increment_value

Repo_increment_value

sql_variant

NULL

Repo_is_computed

Repo_is_computed

bit

NOT NULL

Repo_is_identity

Repo_is_identity

bit

NOT NULL

Repo_is_nullable

Repo_is_nullable

bit

NULL

Repo_is_persisted

Repo_is_persisted

bit

NULL

Repo_seed_value

Repo_seed_value

sql_variant

NULL

Repo_user_type_name

Repo_user_type_name

nvarchar(128)

NULL

Repo_uses_database_collation

Repo_uses_database_collation

bit

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_Referencing_Count

RepoObject_Referencing_Count

int

NULL

RepoObjectColumn_column_id

RepoObjectColumn_column_id

int

NULL

RepoObjectColumn_fullname2

RepoObjectColumn_fullname2

nvarchar(386)

NOT NULL

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_modify_date

SysObject_modify_date

datetime

NULL

SysObject_parent_object_id

SysObject_parent_object_id

int

NOT NULL

tabcol_Description

tabcol_Description

nvarchar(max)

NULL

tabcol_DisplayFolder

tabcol_DisplayFolder

nvarchar(500)

NULL

tabcol_Expression

tabcol_Expression

nvarchar(max)

NULL

tabcol_FormatString

tabcol_FormatString

nvarchar(500)

NULL

tabcol_IsHidden

tabcol_IsHidden

bit

NOT NULL

tabcol_IsKey

tabcol_IsKey

bit

NOT NULL

tabcol_IsUnique

tabcol_IsUnique

bit

NOT NULL

tabcol_SummarizeBy

tabcol_SummarizeBy

nvarchar(500)

NULL

tabcol_Type

tabcol_Type

nvarchar(500)

NULL

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