Merge Tool - AX 2012 - Another useful script to get info

 Say you have a dilemma about the objects you get in a new model that you are going to apply on your instance, wondering if you will have conflicts (overshadowed code with the existing one); I am not going to talk about the standard ways of determining this; what I use instead is a t-sql script that gives me the objects nodes' that are in both models (this is taking into account the AX granularity of storing/handling the objects when):



-- Look at the models
-- select * from ModelManifest

DECLARE @ObjectElement Integer
DECLARE @EIDEModelLower Integer
DECLARE @EIDEModelHigher Integer

SET @EIDEModelLower = ??
SET @EIDEModelHigher = ??

drop table EIDEModelLower
drop table EIDEModelHigher
drop table EIDEModelMerged
drop table EIDEModelElementsMerged

CREATE TABLE [dbo].[EIDEModelElementsMerged](
    [ElementHandle] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [ElementType] [int] NOT NULL,
    [PName] [nvarchar](255) NOT NULL,
    [PType] [int] NOT NULL
) ON [PRIMARY]

-- List of objects that are changed on a specified (input param) model
select parent.ElementHandle, parent.Name, parent.ElementType
    into EIDEModelLower
    from ModelElementData as data
    join modelelement as prop
        on data.ElementHandle = prop.ElementHandle
    join ModelElement as parent
        on parent.ElementHandle = prop.RootHandle
where
    data.ModelId = @EIDEModelLower -- modelId param
group by
    parent.ElementHandle, parent.Name, parent.ElementType
order by
    parent.ElementType

-- List of objects that are changed on a specified (input param) model
select parent.ElementHandle, parent.Name, parent.ElementType
    into EIDEModelHigher
    from ModelElementData as data
    join modelelement as prop
        on data.ElementHandle = prop.ElementHandle
    join ModelElement as parent
        on parent.ElementHandle = prop.RootHandle
where
    data.ModelId = @EIDEModelHigher -- modelId param
group by
    parent.ElementHandle, parent.Name, parent.ElementType
order by
    parent.ElementType

-- list elements existing in Higher model (output display)
-- select data.Name, etypes.ElementTypeName from EIDEModelHigher as data
--    join ElementTypes as etypes
--        on data.ElementType = etypes.ElementType
--    order by etypes.ElementType

-- view same objects
select mLower.*
    into EIDEModelMerged
    from EIDEModelLower as mLower
    join EIDEModelHigher as mHigher
        on    mHigher.ElementHandle = mLower.ElementHandle
order by
    mLower.ElementType, mLower.Name

-- list elements that belongs to both models (output display)      
-- select data.Name, etypes.ElementTypeName
--    from EIDEModelMerged as data
--    join ElementTypes as etypes
--        on data.ElementType = etypes.ElementType

DECLARE elementsCursor CURSOR
    FOR select ElementHandle from EIDEModelMerged

OPEN elementsCursor

FETCH NEXT FROM elementsCursor
    INTO @ObjectElement

truncate table EIDEModelElementsMerged

WHILE @@FETCH_STATUS = 0  
BEGIN

-- Look at the changed subnodes
-- List of elements in a selected object that are changed on both layers
-- This works when both elements are changed on the lower and upper layer and there can be conflicts
-- But this is not enough when a child element that is new belongs to a parent that is changed on both layers
insert into EIDEModelElementsMerged
select data.ElementHandle, prop.Name, prop.ElementType, parent.Name, parent.ElementType
    from ModelElementData as data
    join ModelElement as prop
        on data.ElementHandle = prop.ElementHandle
    join ModelElementData as data_up
        on data_up.ElementHandle = data.ElementHandle
    join ModelElement as parent
        on parent.ElementHandle = prop.RootHandle
where
    data.ModelId = @EIDEModelLower -- lower layer
    and prop.RootHandle = @ObjectElement
    and data_up.ModelId = @EIDEModelHigher -- upper layer
order by prop.ElementHandle

FETCH NEXT FROM elementsCursor
    INTO @ObjectElement

END

-- display output conflicts
select data.Name, data.PName, etypes.ElementTypeName, etypesP.ElementTypeName
    from EIDEModelElementsMerged as data
    join ElementTypes as etypes
        on data.ElementType = etypes.ElementType
    join ElementTypes as etypesP
        on data.PType = etypesP.ElementType
    order by etypesP.ElementTypeName, data.PName, etypes.ElementTypeName

-- display output conflicting objects
select data.PName, etypesP.ElementTypeName
    from EIDEModelElementsMerged as data
    join ElementTypes as etypes
        on data.ElementType = etypes.ElementType
    join ElementTypes as etypesP
        on data.PType = etypesP.ElementType
    group by etypesP.ElementTypeName, data.PName
    order by etypesP.ElementTypeName, data.PName

CLOSE elementsCursor
DEALLOCATE elementsCursor