UET Issues Restoring a Single Database in a Multi Site Environment

It’s not unusual to refresh the dev or test databases from time to time however there are occasions where a customer has a multi-site development environment where users on are actively making changes to some sites and not others.   Recently I ran into this with a customer who has an environment with approximately 16 sites and they urgently needed one of the sites refreshed from a recent backup from live but would not be able to overwrite any other databases.   I knew right away that transfer orders that weren’t complete would have issues with APS and already wrote code to address this and some other issues that I normally see however this customer happened to have hundreds of replication rule to/from this restored site and to make matters worse, a developer had accidentally added a treasure trove of UET fields to various tables in live without first testing the UET in development.  You can be the farm that the developer was disciplined for bringing code straight into production but this doesn’t magically fix the fact that the database I restored into dev is throwing errors on many screens that reference the items table, the customer table, and a hand full of others that I knew about.  It goes without saying that I most likely didn’t find all of them so I was faced with a few different options:

Option 1) Tell customer that the easiest thing (for myself) to do is to overwrite the other databases from copies of live so that they are in sync

Option 2) One by one wait for the UET mismatches to emerge and remove them from the newly restored database.   The gamble would be that only a few total would be found.  This could get annoying

Option 3) Compare the user_fld table of the old copy of the dev database to the newly restored copy (yes of course I backed it up first!) and identify and remove all differences

My original thought was to entertain option 3 because it gave a very clear cut look at every single UET that came over with live that wasn’t in there before restore.   The query was simple and straightforward and in my scenario it returned 32 values.   I then took a look at the table/class relationships and realized very quickly that a high percentage of the UET fields added were on tables that I was pretty sure did not play a role in replication.  The last thing I want to do is waste time plucking out these fields when they will not likely throw an error when performing DML on the table.

I went back to the drawing board and decided that the best way to focus on UET fields that would affect replication was to focus first on replication rules that were enabled and tie them back to the tables that are part of that replication rule.   For example, if Transfer Orders has 15 tables that it uses for replication then I would only want UET fields that have a table/class relationship that would be affected.   In the case of this customer, there were a total of 5 UET fields out of 32 that I knew definitively needed to be dropped.

As usual I am sharing the code as a “work in progress” and it may or may not function in all environments.  I have tested this in Syteline 8 and Syteline 9 (Infor Cloud Suite Industrial).

--what UETs are in source that are NOT in TARGET?

--the reason we don't compare user_fld directly is because it does not tell us if a replication rule is tied to it that is active.
--this method specifically targets the source/destination mismatches when there is a replication rule.
--ideal for removing UET's out of a newly restored db by comparing older copy to newer copy
--ideal for figuring out UET's to add as well!

--replace SOURCE_App with source (in my case the source database was the copy I took from live)
--replace TARGET_App with target (in my case the target database was the old copy from dev)

select reptables.object_name as [Source Table]
,      ucf.fld_name as [Source field]
,      TargetFields.TABLE_NAME as [Target Table]
,      TargetFields.COLUMN_NAME as [Target field]
from      (
select distinct replace(roc.object_name,'_all','') as [object_name]
from SOURCE_App..rep_rule            rr 
join SOURCE_App..rep_object_category roc on rr.category = roc.category
where object_type = 1 and rr.disable_repl = 0) reptables   
join      SOURCE_App..table_class          tc           on reptables.object_name = tc.table_name
join      SOURCE_App..user_class_fld       ucf          on tc.class_name = ucf.class_name
LEFT join (
	select schemacolumns.table_name
	,      schemacolumns.column_name
	from       TARGET_App.information_schema.columns schemacolumns
	INNER JOIN TARGET_App.information_schema.tables  schematables  on schematables.table_name = schemacolumns.table_name and schematables.table_type = 'BASE TABLE'
	)                                   TargetFields on reptables.object_name = TargetFields.table_name and ucf.fld_name = TargetFields.column_name
where TargetFields.TABLE_NAME IS NULL