repo.RepoObjectColumn_RequiredRepoObjectColumnMerge - V

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

RepoObject_guid: 861AAE52-A19B-EB11-84F6-A81E8446D5B0

Description

list of conflicting entries which needs to be merged

mismatch of RepoObjectColumn_guid can create 2 entries per one RepoObjectColumn
this can happen, if the guid exists in the database extended properties and a new guid will be created in the repo

  • roc1 has the right RepoObjectColumn_fullname, but the guid was new created

  • roc2 got the "right" guid from database, but roc2 can’t propagate the fullname into RepoObjectColumn because the RepoObjectColumn_fullname is occupied now we have 2 entries, but we need to merge them

  • keep roc1 (which has the right RepoObjectColumn_name)

    • mark them set is_required_ColumnMerge = 1

  • delete columns with RepoObjectColumn_guid in roc2_RepoObjectColumn_guid

  • set SysObjectColumn_name = RepoObjectColumn_name (for roc1, for marked columns)

  • remove marker where SysObjectColumn_name = RepoObjectColumn_name

Examples

Entity Diagram

entity-repo.repoobjectcolumn_requiredrepoobjectcolumnmerge

Columns

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

int

NOT NULL

int

NOT NULL

uniqueidentifier

NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

uniqueidentifier

NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-repo.repoobjectcolumn_requiredrepoobjectcolumnmerge

References

Referenced Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.repoobjectcolumn_requiredrepoobjectcolumnmerge

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.repoobjectcolumn_requiredrepoobjectcolumnmerge

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.repoobjectcolumn_requiredrepoobjectcolumnmerge

Column Reference Diagram

entity_1_1_colref-repo.repoobjectcolumn_requiredrepoobjectcolumnmerge

Indexes

idx_RepoObjectColumn_RequiredRepoObjectColumnMerge__1

idx_RepoObjectColumn_RequiredRepoObjectColumnMerge__2

idx_RepoObjectColumn_RequiredRepoObjectColumnMerge__3

idx_RepoObjectColumn_RequiredRepoObjectColumnMerge__4

idx_RepoObjectColumn_RequiredRepoObjectColumnMerge__5

idx_RepoObjectColumn_RequiredRepoObjectColumnMerge__6

idx_RepoObjectColumn_RequiredRepoObjectColumnMerge__7

Column Details

_

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_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)


persistence_source_RepoObjectColumn_guid

persistence_source_RepoObjectColumn_guid

uniqueidentifier

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

RepoObjectColumn_guid

RepoObjectColumn_guid

uniqueidentifier

NOT NULL

RepoObjectColumn_name

RepoObjectColumn_name

nvarchar(128)

NOT NULL

roc2_persistence_source_RepoObjectColumn_guid

roc2_persistence_source_RepoObjectColumn_guid

uniqueidentifier

NULL

roc2_RepoObjectColumn_guid

roc2_RepoObjectColumn_guid

uniqueidentifier

NOT NULL

roc2_RepoObjectColumn_name

roc2_RepoObjectColumn_name

nvarchar(128)

NOT NULL

roc2_SysObjectColumn_name

roc2_SysObjectColumn_name

nvarchar(128)

NOT NULL

SysObjectColumn_name

SysObjectColumn_name

nvarchar(128)

NOT NULL

sql_modules_definition

repo.RepoObjectColumn_RequiredRepoObjectColumnMerge - V script
/*
<<property_start>>Description
list of conflicting entries which needs to be merged

mismatch of RepoObjectColumn_guid can create 2 entries per one RepoObjectColumn +
this can happen, if the guid exists in the database extended properties and a new guid will be created in the repo

* roc1 has the right RepoObjectColumn_fullname, but the guid was new created
* roc2 got the "right" guid from database, but roc2 can't propagate the fullname into RepoObjectColumn because the RepoObjectColumn_fullname is occupied
now we have 2 entries, but we need to merge them

what we need to do in xref:sqldb:repo.usp_sync_guid_repoobjectcolumn.adoc[]

* keep roc1 (which has the right RepoObjectColumn_name)
** mark them set is_required_ColumnMerge = 1
* delete columns with RepoObjectColumn_guid in roc2_RepoObjectColumn_guid
* set SysObjectColumn_name = RepoObjectColumn_name (for roc1, for marked columns)
* remove marker where SysObjectColumn_name = RepoObjectColumn_name

<<property_end>>
*/
CREATE View [repo].[RepoObjectColumn_RequiredRepoObjectColumnMerge]
As
Select
    --
    roc1.RepoObject_guid
  , roc1.RepoObjectColumn_guid
  , roc1.is_RepoObjectColumn_name_uniqueidentifier
  , roc1.is_SysObjectColumn_name_uniqueidentifier
  , roc1.persistence_source_RepoObjectColumn_guid
  , roc1.RepoObjectColumn_name
  , roc1.SysObjectColumn_name
  , roc2_persistence_source_RepoObjectColumn_guid = roc2.persistence_source_RepoObjectColumn_guid
  , roc2_RepoObjectColumn_guid                    = roc2.RepoObjectColumn_guid
  , roc2_RepoObjectColumn_name                    = roc2.RepoObjectColumn_name
  , roc2_SysObjectColumn_name                     = roc2.SysObjectColumn_name
From
    repo.RepoObjectColumn     As roc1
    Inner Join
        repo.RepoObjectColumn As roc2
            On
            roc2.RepoObject_guid           = roc1.RepoObject_guid
            And roc2.SysObjectColumn_name  = roc1.RepoObjectColumn_name
            And roc2.RepoObjectColumn_guid <> roc1.RepoObjectColumn_guid