repo.RepoObject_RequiredRepoObjectMerge - V

type: V ( view ), modify_date: 2021-10-02 13:23:34

RepoObject_guid: 52230F14-2E66-EB11-84DD-A81E8446D5B0

Description

list of conflicting entries which needs to be merged

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

  • RepoObject_guid roc1 has the right RepoObject_fullname

  • ro2_RepoObject_guid roc2 got a guid from database, but roc2 can’t propagate the fullname into RepoObject because the RepoObject_fullname is occupied

now we have 2 entries, but we need to merge them

merge is done in [repo].[usp_sync_guid_RepoObject]

'usp_id;Number;Parent_Number: ',8,';',710,';',700

Examples

Entity Diagram

entity-repo.repoobject_requiredrepoobjectmerge

Columns

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

nvarchar(261)

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(261)

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(261)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(261)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-repo.repoobject_requiredrepoobjectmerge

References

Referenced Objects

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.repoobject_requiredrepoobjectmerge

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.repoobject_requiredrepoobjectmerge

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.repoobject_requiredrepoobjectmerge

Column Reference Diagram

entity_1_1_colref-repo.repoobject_requiredrepoobjectmerge

Indexes

idx_RepoObject_RequiredRepoObjectMerge__1

idx_RepoObject_RequiredRepoObjectMerge__2

idx_RepoObject_RequiredRepoObjectMerge__3

idx_RepoObject_RequiredRepoObjectMerge__4

idx_RepoObject_RequiredRepoObjectMerge__5

idx_RepoObject_RequiredRepoObjectMerge__6

Column Details

_

RepoObject_fullname

RepoObject_fullname

nvarchar(261)

NOT NULL

Description

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


RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

Referenced Columns

RepoObject_name

RepoObject_name

nvarchar(128)

NOT NULL

Referenced Columns

RepoObject_schema_name

RepoObject_schema_name

nvarchar(128)

NOT NULL

ro2_RepoObject_fullname

ro2_RepoObject_fullname

nvarchar(261)

NOT NULL

Description

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


ro2_RepoObject_guid

ro2_RepoObject_guid

uniqueidentifier

NOT NULL

Referenced Columns

ro2_RepoObject_name

ro2_RepoObject_name

nvarchar(128)

NOT NULL

Referenced Columns

ro2_RepoObject_schema_name

ro2_RepoObject_schema_name

nvarchar(128)

NOT NULL

ro2_SysObject_fullname

ro2_SysObject_fullname

nvarchar(261)

NOT NULL

Description

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


ro2_SysObject_name

ro2_SysObject_name

nvarchar(128)

NOT NULL

Referenced Columns

SysObject_fullname

SysObject_fullname

nvarchar(261)

NOT NULL

Description

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


SysObject_name

SysObject_name

nvarchar(128)

NOT NULL

Referenced Columns

SysObject_schema_name

SysObject_schema_name

nvarchar(128)

NOT NULL

sql_modules_definition

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

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

* `RepoObject_guid` roc1 has the right RepoObject_fullname
* `ro2_RepoObject_guid` roc2 got a guid from database, but roc2 can't propagate the fullname into RepoObject because the RepoObject_fullname is occupied

now we have 2 entries, but we need to merge them

merge is done in `[repo].[usp_sync_guid_RepoObject]`

`'usp_id;Number;Parent_Number: ',8,';',710,';',700`
<<property_end>>

some history, how we started to investigate:

first we check where the RepoObject PK is used in FK

[source,sql]
------
--Returns logical foreign key information
EXEC sp_fkeys @pktable_name = N'RepoObject', @pktable_owner = N'repo';
------

we should care about

repo	RepoObject_persistence	target_RepoObject_guid
repo	ProcedureInstance	Procedure_RepoObject_guid

we will not care about

repo	Index_virtual	parent_RepoObject_guid
repo	RepoObject_SqlModules	RepoObject_guid
repo	RepoObjectColumn	RepoObject_guid
repo	RepoObjectProperty	RepoObject_guid
repo	RepoObjectSource_FirstResultSet	RepoObject_guid
repo	RepoObjectSource_QueryPlan	RepoObject_guid

*/
CREATE View repo.RepoObject_RequiredRepoObjectMerge
As
Select
    ro1.RepoObject_guid
  , ro2_RepoObject_guid        = ro2.RepoObject_guid
  , ro1.RepoObject_fullname
  , ro2_RepoObject_fullname    = ro2.RepoObject_fullname
  , ro1.SysObject_fullname
  , ro2_SysObject_fullname     = ro2.SysObject_fullname
  , ro1.RepoObject_name
  , ro1.RepoObject_schema_name
  , ro1.SysObject_name
  , ro1.SysObject_schema_name
  , ro2_RepoObject_name        = ro2.RepoObject_name
  , ro2_RepoObject_schema_name = ro2.RepoObject_schema_name
  , ro2_SysObject_name         = ro2.SysObject_name
From
    repo.RepoObject     As ro1
    Inner Join
        repo.RepoObject As ro2
            On
            ro2.SysObject_fullname  = ro1.RepoObject_fullname
            And ro2.RepoObject_guid <> ro1.RepoObject_guid