repo.SysColumn_RepoObjectColumn_via_name - V

type: V ( view ), modify_date: 2022-01-05 18:06:34

RepoObject_guid: 5F90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo.syscolumn_repoobjectcolumn_via_name

Columns

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

nvarchar(max)

NULL

bit

NULL

sysname

NULL

nvarchar(max)

NULL

tinyint

NULL

int

NULL

bit

NULL

sql_variant

NULL

bit

NOT NULL

bit

NOT NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

int

NULL

bit

NULL

int

NULL

uniqueidentifier

NULL

int

NULL

nvarchar(max)

NULL

bit

NULL

nvarchar(128)

NULL

nvarchar(max)

NULL

tinyint

NULL

int

NULL

sql_variant

NULL

bit

NULL

bit

NULL

bit

NULL

bit

NULL

sql_variant

NULL

nvarchar(128)

NULL

nvarchar(128)

NULL

bit

NULL

nvarchar(261)

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

bigint

NULL

sql_variant

NULL

int

NOT NULL

sysname

NULL

nvarchar(261)

NULL

int

NOT NULL

nvarchar(128)

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

char(2)

NULL

int

NULL

nvarchar(128)

NULL

nvarchar(182)

NULL

sysname

NULL

bit

NULL

Foreign Key Diagram

entity_1_1_fk-repo.syscolumn_repoobjectcolumn_via_name

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.syscolumn_repoobjectcolumn_via_name

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.syscolumn_repoobjectcolumn_via_name

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.syscolumn_repoobjectcolumn_via_name

Column Reference Diagram

entity_1_1_colref-repo.syscolumn_repoobjectcolumn_via_name

Indexes

idx_SysColumn_RepoObjectColumn_via_name__1

idx_SysColumn_RepoObjectColumn_via_name__2

idx_SysColumn_RepoObjectColumn_via_name__3

idx_SysColumn_RepoObjectColumn_via_name__4

Column Details

_

default_definition

default_definition

nvarchar(max)

NULL

default_is_system_named

default_is_system_named

bit

NULL

default_name

default_name

sysname

NULL

Referenced Columns

definition

definition

nvarchar(max)

NULL

Referenced Columns

generated_always_type

generated_always_type

tinyint

NULL

graph_type

graph_type

int

NULL

Referenced Columns

has_different_sys_names

has_different_sys_names

bit

NULL

Description

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


increment_value

increment_value

sql_variant

NULL

is_computed

is_computed

bit

NOT NULL

Referenced Columns

is_identity

is_identity

bit

NOT NULL

Referenced Columns

is_nullable

is_nullable

bit

NULL

Referenced Columns

is_persisted

is_persisted

bit

NULL

Referenced Columns

is_query_plan_expression

is_query_plan_expression

bit

NULL

is_repo_managed

is_repo_managed

bit

NULL

Referenced Columns

is_RepoObjectColumn_name_uniqueidentifier

is_RepoObjectColumn_name_uniqueidentifier

int

NULL

Description

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

NULL

Description

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


persistence_source_RepoObjectColumn_guid

persistence_source_RepoObjectColumn_guid

uniqueidentifier

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

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

NULL

Repo_is_identity

Repo_is_identity

bit

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_fullname

Repo_user_type_fullname

nvarchar(128)

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)

NULL

Description

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


RepoObject_guid

RepoObject_guid

uniqueidentifier

NULL

RepoObjectColumn_name

RepoObjectColumn_name

nvarchar(128)

NULL

RowNumberOverName

RowNumberOverName

bigint

NULL

seed_value

seed_value

sql_variant

NULL

Referenced Columns

SysObject_column_id

SysObject_column_id

int

NOT NULL

SysObject_fullname

SysObject_fullname

nvarchar(261)

NULL

Description

(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))


SysObject_id

SysObject_id

int

NOT NULL

Referenced Columns

SysObject_name

SysObject_name

nvarchar(128)

NULL

SysObject_RepoObject_guid

SysObject_RepoObject_guid

uniqueidentifier

NULL

SysObject_RepoObjectColumn_guid

SysObject_RepoObjectColumn_guid

uniqueidentifier

NULL

SysObject_schema_name

SysObject_schema_name

nvarchar(128)

NULL

SysObject_type

SysObject_type

char(2)

NULL

SysObjectColumn_column_id

SysObjectColumn_column_id

int

NULL

SysObjectColumn_name

SysObjectColumn_name

nvarchar(128)

NULL

user_type_fullname

user_type_fullname

nvarchar(182)

NULL

user_type_name

user_type_name

sysname

NULL

uses_database_collation

uses_database_collation

bit

NULL

sql_modules_definition

repo.SysColumn_RepoObjectColumn_via_name - V script
CREATE View repo.SysColumn_RepoObjectColumn_via_name
As
--
Select
    sc.SysObject_id
  , sc.SysObject_column_id
  , ro.is_repo_managed
  , ro.RepoObject_fullname
  , ro.SysObject_fullname
  , roc.has_different_sys_names
  , roc.is_query_plan_expression
  , roc.is_RepoObjectColumn_name_uniqueidentifier
  , roc.is_SysObjectColumn_missing
  , roc.is_SysObjectColumn_name_uniqueidentifier
  , roc.persistence_source_RepoObjectColumn_guid
  , roc.Referencing_Count
  , roc.Repo_default_definition
  , roc.Repo_default_name
  , roc.Repo_default_is_system_named
  , 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_guid
  , roc.RepoObjectColumn_name
  , roc.SysObjectColumn_column_id
  , roc.SysObjectColumn_name
  , sc.default_definition
  , sc.default_is_system_named
  , sc.default_name
  , sc.definition
  , sc.generated_always_type
  , sc.graph_type
  , sc.is_computed
  , sc.is_identity
  , sc.is_nullable
  , sc.is_persisted
  , sc.seed_value
  , sc.increment_value
  , sc.SysObject_column_name
  , sc.SysObject_name
  , SysObject_RepoObject_guid = Coalesce ( sc.SysObject_RepoObject_guid, ro.RepoObject_guid )
  , sc.SysObject_RepoObjectColumn_guid
  , sc.SysObject_schema_name
  , sc.SysObject_type
  , sc.user_type_fullname
  , sc.user_type_name
  , sc.uses_database_collation
  --sometimes we have columns with same name but different guid in repo.RepoObjectColumn, additional columns should be deleted in [repo].[usp_sync_guid_RepoObjectColumn]
  --here we prepare, which to keep (RowNumberOverName = 1)
  --we should keep RepoObjectColumn_name, because there could be PK defined and other properties
  , RowNumberOverName         = Row_Number () Over ( Partition By
                                                         Coalesce ( sc.SysObject_RepoObject_guid, ro.RepoObject_guid )
                                                       , sc.SysObject_column_name
                                                     Order By
                                                         roc.is_RepoObjectColumn_name_uniqueidentifier
                                                       , roc.is_SysObjectColumn_name_uniqueidentifier
                                                   )
From
    repo_sys.SysColumn        As sc
    Left Join
        repo.RepoObject       As ro
            On
            ro.SysObject_fullname2   = sc.SysObject_fullname2
            And ro.is_ssas           = 0
            And ro.is_external       = 0

    Left Join
        repo.RepoObjectColumn As roc
            On
            roc.SysObjectColumn_name = sc.SysObject_column_name
            And roc.RepoObject_guid  = ro.RepoObject_guid