Comparing TEST and LIVE schema changes between MEDITECH Updates
Data Repository Talk

Comparing TEST and LIVE schema changes between MEDITECH Updates


This article updates the MT KB Article scripts to fit both the M-AT and NPR Platforms


During the MEDITECH Data Repository update process, one step in the DR Go LIVE Readiness tickets is to run the Schema Compare tool or execute KB #33386 scripts to find the changes that are being made to your DR database(s). These scripts allow end users to review any tables being removed or added with the update as well as any column removals, additions, datatype or length changes between the updated TEST schema and the current LIVE schema.  

However, these scripts have been written using the older version of the DR System tables, SysDrTables and SysDrColumns, which are no longer kept up to date in the newer versions of the MEDITECH code. The newer tables, DrTableMain, DrTableColumns and DrTable_Main, DrTable_Columns are a better alternative to use.

Below are updated scripts that utilize the DrTable system tables for both the M-AT and NPR databases. These scripts can be used for all platforms in MEDITECH. Sites who are on the Client Server or MAGIC platforms need only the Livendb code, while M-AT and EXPANSE sites will need both the Livendb and Livefdb code. Please remember that when using these scripts, end users will want to change the database reference to reflect the naming conventions for their live and test databases.

--Livendb DELETED TABLES
                     PRINT ('Deleted tables')
                                 live.TableID [TableID],
                                 live.TableName [TableName]
                     FROM    Livendb.dbo.DrTableMain live
                                  LEFT JOIN Testndb.dbo.DrTableMain test
                                        ON test.TableID = live.TableID
                     WHERE   test.TableID IS NULL
                     ORDER BY live.ApplID,
                                 live.TableID

 --Livefdb DELETED TABLES
                      PRINT ('Deleted tables')
                      SELECT  live.Application_FocApplID [Application],
                                   live.DrTableID [TableID],
                                  live.TableName [TableName]
                      FROM    Livefdb.dbo.DrTable_Main live
                                   LEFT JOIN Testfdb.dbo.DrTable_Main test
                                          ON test.DrTableID = live.DrTableID
                      WHERE   test.DrTableID IS NULL
                      ORDER BY live.Application_FocApplID,
                                   live.DrTableID

 --Livendb NEW TABLES
                      PRINT ('New tables')
                      SELECT  test.ApplID [Application],
                                   test.TableID [TableID],
                                   test.TableName [TableName]
                      FROM    Testndb.dbo.DrTableMain test
                                   LEFT JOIN Livendb.dbo.DrTableMain live
                                          ON test.TableID = live.TableID
                      WHERE   live.TableID IS NULL
                      ORDER BY test.ApplID,
                                   test.TableID

 --Livefdb NEW TABLES
                      PRINT ('New tables')
                      SELECT  test.Application_FocApplID [Application],
                                   test.DrTableID [TableID],
                                   test.TableName [TableName]
                      FROM    Testfdb.dbo.DrTable_Main test
                                   LEFT JOIN Livefdb.dbo.DrTable_Main live
                                          ON test.DrTableID = live.DrTableID
                      WHERE   live.DrTableID IS NULL
                      ORDER BY test.Application_FocApplID,
                                   test.DrTableID

 --Livendb LENGTH CHANGES
                      PRINT ('Length changes')
                      SELECT  testtab.TableName [TableName],
                                   test.TableID [TableID],
                                   test.ColumnName [ColumnName],
                                   test.ElementLength [NewLength],
                                   live.ElementLength [OldLength]
                      FROM    ( SELECT    TableID,
                                                        TableName
                                     FROM      Testndb.dbo.DrTableMain ) AS testtab
                                   INNER JOIN Testndb.dbo.DrTableColumns test
                                          ON testtab.TableID = test.TableID
                                   INNER JOIN Livendb.dbo.DrTableColumns live
                                          ON test.TableID = live.TableID
                                             AND test.ColumnName = live.ColumnName
                                             AND test.NprElement = live.NprElement
                      WHERE   LEFT(test.NprElement, 1) <> '~'
                                   AND test.ElementLength <> live.ElementLength

 --Livefdb LENGTH CHANGES
                      PRINT ('Length changes')
                      SELECT  testtab.TableName [TableName],
                                   test.DrTableID [TableID],
                                   test.ColumnName [ColumnName],
                                   test.ColumnLength [NewLength],
                                   live.ColumnLength [OldLength]
                      FROM    ( SELECT    DrTableID,
                                                        TableName
                                   FROM      Testfdb.dbo.DrTable_Main ) AS testtab
                                   INNER JOIN Testfdb.dbo.DrTable_Columns test
                                          ON testtab.DrTableID = test.DrTableID
                                   INNER JOIN Livefdb.dbo.DrTable_Columns live
                                          ON test.DrTableID = live.DrTableID
                                             AND test.ColumnName = live.ColumnName
                                             AND test.ColumnField = live.ColumnField
                      WHERE   LEFT(test.ColumnField, 1) <> '~'
                                   AND test.ColumnLength <> live.ColumnLength

 --Livendb TABLE NAME CHANGES
                      PRINT ('Table name changes')
                      SELECT  test.ApplID [Application],
                                   test.TableName [NewName],
                                   live.TableName [OldName]
                      FROM    Livendb.dbo.DrTableMain live
                                   INNER JOIN Testndb.dbo.DrTableMain tes
                                         ON live.TableID = test.TableID
                                             AND live.TableName <> test.TableName

 --Livefdb TABLE NAME CHANGES
                      PRINT ('Table name changes')
                      SELECT  test.Application_FocApplID [Application],
                                   test.TableName [NewName],
                                   live.TableName [OldName]
                      FROM    Livefdb.dbo.DrTable_Main live
                                   INNER JOIN Testfdb.dbo.DrTable_Main test
                                          ON live.DrTableID = test.DrTableID
                                             AND live.TableName <> test.TableName

 --Livendb DATATYPE CHANGES
                      PRINT ('Datatype changes')
                      SELECT  testtab.TableName [TableName],
                                   test.TableID [TableID],
                                   test.ColumnName [ColumnName],
                                   test.ElementDatatype [NewDataType],
                                   live.ElementDatatype [OldDataType]
                      FROM    ( SELECT    TableID,
                                                        TableName
                                     FROM      Testndb.dbo.DrTableMain ) AS testtab
                                   INNER JOIN Testndb.dbo.DrTableColumns test
                                          ON testtab.TableID = test.TableID
                                   INNER JOIN Livendb.dbo.DrTableColumns live
                                          ON test.TableID = live.TableID
                                             AND test.ColumnName = live.ColumnName
                                             AND test.NprElement = live.NprElement
                      WHERE   LEFT(test.NprElement, 1) <> '~'
                                   AND test.ElementDatatype <> live.ElementDatatype

 --Livefdb DATATYPE CHANGES
                      PRINT ('Datatype changes')
                      SELECT  testtab.TableName [TableName],
                                   test.DrTableID [TableID],
                                   test.ColumnName [ColumnName],
                                   test.ColumnDataType_DrDataTypeID [NewDataType],
                                   live.ColumnDataType_DrDataTypeID [OldDataType]
                      FROM    ( SELECT    DrTableID,
                                                        TableName
                                     FROM      Testfdb.dbo.DrTable_Main ) AS testtab
                                   INNER JOIN Testfdb.dbo.DrTable_Columns test
                                          ON testtab.DrTableID = test.DrTableID
                                   INNER JOIN Livefdb.dbo.DrTable_Columns live
                                          ON test.DrTableID = live.DrTableID
                                             AND test.ColumnName = live.ColumnName
                                             AND test.ColumnField = live.ColumnField
                      WHERE   LEFT(test.ColumnField, 1) <> '~'
                                   AND test.ColumnDataType_DrDataTypeID <> live.ColumnDataType_DrDataTypeID

 --Livendb COLUMN NAME CHANGES
                      PRINT ('Column name changes')
                      SELECT  testtab.TableName [TableName],
                                   test.TableID [TableID],
                                   test.ColumnName [NewColumnName],
                                   live.ColumnName [OldColumName],
                                   live.NprElement
                      FROM    ( SELECT    TableID,
                                                        TableName
                                     FROM      Testndb.dbo.DrTableMain ) AS testtab
                                  INNER JOIN Testndb.dbo.DrTableColumns test
                                          ON testtab.TableID = test.TableID
                                   INNER JOIN Livendb.dbo.DrTableColumns live
                                          ON test.TableID = live.TableID
                                             AND test.NprElement = live.NprElement
                                             AND test.ColumnName <> live.ColumnName
                      WHERE   LEFT(test.NprElement, 1) <> '~'
                                   AND test.NprElement <> 'N/A'

 --Livenfdb COLUMN NAME CHANGES
                      PRINT ('Column name changes')
                      SELECT  testtab.TableName [TableName],
                                   test.DrTableID [TableID],
                                   test.ColumnName [NewColumnName],
                                   live.ColumnName [OldColumName],
                                   live.ColumnField
                      FROM    ( SELECT    DrTableID,
                                                        TableName
                                      FROM      Testfdb.dbo.DrTable_Main ) AS testtab
                                   INNER JOIN Testfdb.dbo.DrTable_Columns test
                                          ON testtab.DrTableID = test.DrTableID
                                   INNER JOIN Livefdb.dbo.DrTable_Columns live
                                          ON test.DrTableID = live.DrTableID
                                             AND test.ColumnField = live.ColumnField
                                             AND test.ColumnName <> live.ColumnName
                      WHERE   LEFT(test.ColumnField, 1) <> '~'
                                   AND test.ColumnField <> 'N/A'

 --Livendb COLUMNS REMOVED
                      PRINT ('Columns removed')
                      SELECT  testtab.TableID,
                                   testtab.TableName [Table Name],
                                   livecol.ColumnName [Column Name],
                                   livecol.NprElement
                      FROM    ( SELECT    TableID,
                                                        TableName
                                     FROM      Testndb.dbo.DrTableMain ) AS testtab
                                   INNER JOIN Livendb.dbo.DrTableColumns livecol
                                          ON testtab.TableID = livecol.TableID
                                   LEFT JOIN Testndb.dbo.DrTableColumns testcol
                                          ON testcol.TableID = livecol.TableID
                                             AND testcol.NprElement = livecol.NprElement
                      WHERE   LEFT(livecol.NprElement, 1) <> '~'
                                   AND testcol.TableID IS NULL

 --Livefdb COLUMNS REMOVED
                      PRINT ('Columns removed')
                      SELECT  testtab.DrTableID,
                                   testtab.TableName [Table Name],
                                   livecol.ColumnName [Column Name],
                                   livecol.ColumnField
                      FROM    ( SELECT    DrTableID,
                                                        TableName
                                     FROM      Testfdb.dbo.DrTable_Main ) AS testtab
                                   INNER JOIN Livefdb.dbo.DrTable_Columns livecol
                                          ON testtab.DrTableID = livecol.DrTableID
                                   LEFT JOIN Testfdb.dbo.DrTable_Columns testcol
                                          ON testcol.DrTableID = livecol.DrTableID
                                             AND testcol.ColumnField = livecol.ColumnField
                      WHERE   LEFT(livecol.ColumnField, 1) <> '~'
                                   AND testcol.DrTableID IS NULL

 --Livendb NEW COLUMNS
                      PRINT ('New columns')
                      SELECT  livetab.ApplID,
                                    livetab.TableName [Table Name],
                                   livetab.TableID,
                                   testcol.ColumnName [Column Name],
                                   testcol.NprElement [NPR Element]
                      FROM    ( SELECT    TableID,
                                                        TableName,
                                                        ApplID
                                     FROM      Livendb.dbo.DrTableMain ) AS livetab
                                   INNER JOIN Testndb.dbo.DrTableColumns testcol
                                          ON livetab.TableID = testcol.TableID
                                   LEFT JOIN Livendb.dbo.DrTableColumns livecol
                                          ON testcol.TableID = livecol.TableID
                                             AND testcol.NprElement = livecol.NprElement
                      WHERE   LEFT(testcol.NprElement, 3) <> '~""'
                                   AND livecol.TableID IS NULL
                      ORDER BY livetab.ApplID,
                                   livetab.TableName,
                                   livetab.TableID,
                                   testcol.ColumnName

 --Livefdb NEW COLUMNS
                      PRINT ('New columns')
                      SELECT  livetab.Application_FocApplID,
                                   livetab.TableName [Table Name],
                                   livetab.DrTableID,
                                   testcol.ColumnName [Column Name],
                                   testcol.ColumnField [Column Field]
                      FROM    ( SELECT    DrTableID,
                                                        TableName,
                                                        Application_FocApplID
                                     FROM      Livefdb.dbo.DrTable_Main ) AS livetab
                                   INNER JOIN Testfdb.dbo.DrTable_Columns testcol
                                          ON livetab.DrTableID = testcol.DrTableID
                                   LEFT JOIN Livefdb.dbo.DrTable_Columns livecol
                                          ON testcol.DrTableID = livecol.DrTableID
                                             AND testcol.ColumnField = livecol.ColumnField
                      WHERE   LEFT(testcol.ColumnField, 3) <> '~""'
                                   AND livecol.DrTableID IS NULL
                      ORDER BY livetab.Application_FocApplID,
                                   livetab.TableName,
                                   livetab.DrTableID,
                                   testcol.ColumnName


By Taylor Solari at 22 Jan 2019, 13:14 PM

Comments

 

Post a comment

Please correct the following:
Tags
Authors
Glen D'Abate
Ian Proffer
Bill Presley
Jamie Gerardo
Geoff Grouten
Ken MacPherson
Alexis Donnaruma
Taylor Solari
Theresa Jasset
Howard Minor
Categories
Tutorial
Project
Company Information
Reports
News
Event


rss feedRSS