Data Dictionary comparison between DEV and PROD

DD synchronisation is important during a project.  It’s vital that the DD you are testing against (DVDD910) is the same as DD910.

So, ensuring that you are not missing DD items is easy, but what about changes.  Changes to length, lookup items, description, decimal places?  Well, do I have a convoluted process for you, firstly identify them with this SQL:

select * from dd910.f9210 t1 where not exists
(select 1  from dvdd910.f9210 t2
where  t1.frdtai = t2.frdtai
AND t1.FRCLAS = t2.FRCLAS
AND t1.FRDTAT = t2.FRDTAT
AND t1.FRDTAS = t2.FRDTAS
AND t1.FRDTAD = t2.FRDTAD
AND t1.FRPDTA = t2.FRPDTA
AND t1.FRARRN = t2.FRARRN
AND t1.FRDVAL = t2.FRDVAL
AND t1.FRLR = t2.FRLR
AND t1.FRCDEC = t2.FRCDEC
AND t1.FRDRUL = t2.FRDRUL
AND t1.FRDRO1 = t2.FRDRO1
AND t1.FRERUL = t2.FRERUL
AND t1.FRERO1 = t2.FRERO1
AND t1.FRERO2 = t2.FRERO2
AND t1.FRHLP1 = t2.FRHLP1
AND t1.FRHLP2 = t2.FRHLP2
AND t1.FRNNIX = t2.FRNNIX
AND t1.FRNSY = t2.FRNSY
AND t1.FRRLS = t2.FRRLS
AND t1.FROWDI = t2.FROWDI
AND t1.FROWTP = t2.FROWTP
AND t1.FRCNTT = t2.FRCNTT
AND t1.FRSCFG = t2.FRSCFG
AND t1.FRUPER = t2.FRUPER
AND t1.FRALBK = t2.FRALBK
AND t1.FROWER = t2.FROWER
AND t1.FROER1 = t2.FROER1
AND t1.FROER2 = t2.FROER2
AND t1.FROWDR = t2.FROWDR
AND t1.FRODR1 = t2.FRODR1
AND t1.FRDBID = t2.FRDBID
AND t1.FRBFDN = t2.FRBFDN
AND t1.FREBID = t2.FREBID
AND t1.FRBFEN = t2.FRBFEN
AND t1.FRSFID = t2.FRSFID
AND t1.FRSFMN = t2.FRSFMN
AND t1.FRBVID = t2.FRBVID
AND t1.FRBVNM = t2.FRBVNM
AND t1.FRPLFG = t2.FRPLFG
AND t1.FRDDID = t2.FRDDID
AND t1.FRAUIN = t2.FRAUIN
) order by t1.frdtai desc;

Nice, so now we have our list of problem items, we need to then identify what is different, that is done by copying the results of the above into a spreadsheet.

You’ll have two tabs, one with the contents of the above and the other with these:

select * from dvdd910.f9210 t1 where not exists
(select 1  from dd910.f9210 t2

 

So now with those two tabs created and labelled, you can run the macro below.  This is going highlight the differences and also concatenate the results to the original

Sub compareSheets(shtBefore As String, shtAfter As String)
Dim mycell As Range
Dim mydiffs As Integer

'If current cell is not a date then proceed (else skip and go to next), then 'if not same as corresponding cell in sheet After,
'mark as yellow and repeat until entire range is used

For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
If Not IsDate(mycell) Then
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then
        mycell.Interior.Color = vbGreen
        mycell.Value = mycell.Value & "||" & ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value
        mydiffs = mydiffs + 1
    End If
End If
Next

'Display a message box stating the number of differences found
MsgBox mydiffs & " differences found", vbInformation
ActiveWorkbook.Sheets(shtAfter).Select
End Sub

Sub RunCompare()

Call compareSheets("DVDD910", "DD910")

End Sub

Note that you might need to ensure that there is the same record count in both tabs, otherwise everything will be marked different.