Schema comparison between versions of MsDyn365FO

In this post I want to describe how you can perform a SQL schema comparison during update/upgrade of Microsoft Dynamics 365 for Finance and Operation.

We recently had the challenge that we needed to know which fields had been deleted on the way from version 7.1 to 8.1. The reason behind this topic is that there could be some problems during pack/unpack when you have an reduced “food print” of a record.

Off course there are different tools to do schema comparison between two (or more) different database, but I simply hadn´t enough disk space to store the database twice. After some brainstorming I figured out that I have everything I need in the MSDYN365FO database. A perfect base for this task are the tables TableIdTable and FieldIdTable. 

One major thing is that you have to store the old/current schema before you start the update/upgrade. In my case the old/current version was 7.1 with platform update 20 so I used the following SQL to save the schema: 

CREATE TABLE TableAndFieldNames1611 (TableName [nvarchar](250),FieldName [nvarchar](250),EntityName[nvarchar](250),ViewName[nvarchar](250),SQLTableName[nvarchar](250));

INSERT INTO TableAndFieldNames1611 select TABLEIDTABLE.NAME TableName, TABLEFIELDIDTABLE.NAME FieldName, (select distinct DMFENTITY.TARGETENTITY from DMFENTITY where DMFENTITY.TARGETENTITY = TABLEIDTABLE.NAME) as EntityName, (select distinct sys.views.name from sys.views where sys.views.name = TABLEIDTABLE.NAME) as ViewName, (select distinct sys.tables.name from sys.tables where sys.tables.name = TABLEIDTABLE.NAME) as SQLTableName from TABLEFIELDIDTABLE, TABLEIDTABLE where TABLEIDTABLE.ID = TABLEFIELDIDTABLE.TABLEID 

After the update/upgrade you have to save the new schema to compare it with the old schema and also to use it after following updates. To save the schema of version 8.1.1 with platform update 21 I used the following SQL. 

CREATE TABLE TableAndFieldNames811_PU21 (TableName [nvarchar](250),FieldName [nvarchar](250),EntityName[nvarchar](250),ViewName[nvarchar](250),SQLTableName[nvarchar](250));

INSERT INTO TableAndFieldNames811_PU21 select TABLEIDTABLE.NAME TableName, TABLEFIELDIDTABLE.NAME FieldName, (select distinct DMFENTITY.TARGETENTITY from DMFENTITY where DMFENTITY.TARGETENTITY = TABLEIDTABLE.NAME) as EntityName, (select distinct sys.views.name from sys.views where sys.views.name = TABLEIDTABLE.NAME) as ViewName, (select distinct sys.tables.name from sys.tables where sys.tables.name = TABLEIDTABLE.NAME) as SQLTableName from TABLEFIELDIDTABLE, TABLEIDTABLE where TABLEIDTABLE.ID = TABLEFIELDIDTABLE.TABLEID 

Now you have all data you need in place, so you can start the comparison. I was especially interested in missing field, but you are also able to query new field after the update/upgrade. This could be helpful to find “undocumented” new features…

The statement I used to query the missing fields is the following one. For the additional tasks to check which “tables” is actually a view, I used Excel. 

select Old.* from TableAndFieldNames1611 Old where not exists (select New.* from TableAndFieldNames811_PU21 New where New.TableName = Old.TableName and New.FieldName = Old.FieldName) and not Old.TableName like ‘%Staging’ and not Old.TableName like ‘%Entity’ and Old.EntityName is null and (Old.SQLTableName is not null or Old.ViewName is not null) order by Old.TableName ,old.FieldName

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s