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
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_RepoObject_RequiredRepoObjectMerge__1
-
IndexSemanticGroup: no_group
-
[column-RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_RequiredRepoObjectMerge__2
-
IndexSemanticGroup: no_group
-
[column-ro2_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_RequiredRepoObjectMerge__3
-
IndexSemanticGroup: no_group
-
[column-SysObject_schema_name]; nvarchar(128)
-
[column-ro2_SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_RequiredRepoObjectMerge__4
-
IndexSemanticGroup: no_group
-
[column-SysObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_RequiredRepoObjectMerge__5
-
IndexSemanticGroup: no_group
-
[column-RepoObject_schema_name]; nvarchar(128)
-
[column-RepoObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_RequiredRepoObjectMerge__6
-
IndexSemanticGroup: no_group
-
[column-ro2_RepoObject_schema_name]; nvarchar(128)
-
[column-ro2_RepoObject_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
RepoObject_fullname
RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
ro2_RepoObject_fullname
ro2_RepoObject_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
ro2_SysObject_fullname
ro2_SysObject_fullname |
|
|
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
SysObject_fullname
SysObject_fullname |
|
|
(concat('[',[SysObject_schema_name],'].[',[SysObject_name],']'))
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