Modified objects between two AX 2012 databases

In case if you were wondering what are the modified objects between two AX 2012 databases (renamed, or deleted entries based on OriginId), we could use the Linked Server feature of Microsoft SQL Server to find out.

Our scenario was an AX 2012 RTM Feature Pack installation that is being upgraded to R3 version, and we wanted to find out which objects have been renamed or removed (either DEL_ prefix, or complete deletion) between the two instances with my colleague, Peter Prokopecz.

The solution was to open SSMS and under Server Objects > Linked Servers add a connection for the other SQL instance, then we could use the queries below to find out which are the modified objects between two AX 2012 databases of any versions.

-- Find all renamed objects between two AX 2012 databases by OriginId match
SELECT DISTINCT r1element.ElementHandle
       ,r1elementtypes.TreeNodeName AS [TreeNodeName]
       ,r1element.Name              AS [AOTNameR1]
       ,r3element.Name              AS [AOTNameR3]
       ,parentElement.name          AS [ParentAOTName]
       ,parentType.treenodename     AS [ParentAOTType]
       ,r1layer.Name                AS [AOTLayer]
       ,r1element.Origin            AS [OriginId]
       ,r1element.AxId              AS [ApplObjectId]
       FROM [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelElementData] r1elementdata
JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelElement] r1element
    ON r1elementdata.ElementHandle = r1element.ElementHandle
JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[Layer] r1layer
    ON r1layer.Id = r1elementdata.LayerId
JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ElementTypes] r1elementtypes 
    ON r1elementtypes.ElementType = r1element.ElementType
JOIN [AX2012Upgrade_model].[dbo].[ModelElement] r3element 
    ON r3element.origin = r1element.origin
JOIN [AX2012Upgrade_model].[dbo].[ModelElementData] r3elementdata
    ON r3elementdata.ElementHandle = r3element.ElementHandle
LEFT JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelelementData] Parent
    ON parent.elementhandle = r1element.parenthandle
LEFT JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelElement] parentElement
    ON parent.ElementHandle = parentElement.ElementHandle
LEFT JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ElementTypes] parentType
    ON parentType.ElementType = parentElement.ElementType
WHERE r1element.Origin <> '00000000-0000-0000-0000-000000000000'
AND r3element.Origin <> '00000000-0000-0000-0000-000000000000'
AND r1element.Name <> r3element.Name

-- Find elements which have been removed between two AX 2012 databases based on OriginId
SELECT r1element.ElementHandle
       ,r1elementtypes.TreeNodeName	AS [TreeNodeName]
       ,r1element.Name				AS [AOTName]
       ,parentType.treenodename		AS [ParentAOTType]
       ,parentElement.name			AS [ParentAOTName]
       ,r1layer.Name				AS [AOTLayer]
       ,r1element.Origin			AS [OriginId]
       ,r1element.AxId				AS [ApplObjectId]
       FROM [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelElementData] r1elementdata
JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelElement] r1element
	ON r1elementdata.ElementHandle = r1element.ElementHandle
JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[Layer] r1layer
	ON r1layer.Id = r1elementdata.LayerId
JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ElementTypes] r1elementtypes
	ON r1elementtypes.ElementType = r1element.ElementType
LEFT JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelelementData] Parent
	ON parent.elementhandle = r1element.parenthandle
LEFT JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ModelElement] parentElement
	ON parent.ElementHandle = parentElement.ElementHandle
LEFT JOIN [R1SQLINSTANCE].[AX2012R1].[dbo].[ElementTypes] parentType
	ON parentType.ElementType = parentElement.ElementType
WHERE r1element.Origin <> '00000000-0000-0000-0000-000000000000'
AND r1element.Origin NOT IN
(SELECT
       Origin AS [OriginId]
       FROM [AX2012Upgrade_model].[dbo].[ModelElementData] r1elementdata
JOIN [AX2012Upgrade_model].[dbo].[ModelElement] r1element
	ON r1elementdata.ElementHandle = r1element.ElementHandle
JOIN [AX2012Upgrade_model].[dbo].[Layer] r1layer
	ON r1layer.Id = r1elementdata.LayerId
JOIN [AX2012Upgrade_model].[dbo].[ElementTypes] r1elementtypes
	ON r1elementtypes.ElementType = r1element.ElementType
WHERE r1element.Origin <> '00000000-0000-0000-0000-000000000000')
ORDER BY AOTName

These are perfect for identifying for example enums, EDTs or tables which have been deprecated by Microsoft. If you want to use Version Control for your code upgrade, you could just branch off the original R1 version, rename the objects and then you would be able to keep the historical changes on those objects.