How to get the Database Schema from a customer and compare with a Database Schema from a Demo database?

Version 2

    Details

    How to get the Database Schema from a customer and compare with a Database Schema from a Demo database?

     

    - In most cases, it is sufficient to get a blank structural database from a customer as this contains customization, screens, and user-defined fields.
    - A blank structural database will be created upon the DATADICT definition means that field definitions in OPMGR, FILTERS, FORMS etc. are built upon the GoldMine definition and not upon the currently used database

     

    >>  This means if a certain behavior is still not replicable or identifiable with the blank structural database it might be necessary to get a full database schema from a customer's GoldMine database in order to compare with the database schema of the demo database as the customer may have changed something in the back end, for example, datatype TEXT for notes fields into IMAGE datatype

     

     


    Resolution

    [STEPS]

     

    1. Get from the customer an Microsoft Excel sheet from the following query

     

     

    SELECT TABLE_CATALOG, TABLE_NAME as tablename,COLUMN_NAME as columnname, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, *

     

    FROM INFORMATION_SCHEMA.COLUMNS

     

    WHERE TABLE_CATALOG = '<ENTER GoldMine database NAME>' order by tablename,COLUMNNAME

     

     

    This leads to a query result similar to the following from a demo database but this time ‘skips’ the DATADICT definition which was used by GoldMine when requesting the blank structural database

     


     

     

    2. Get the same query result from a fresh demo database from the identical version of GoldMine as a Microsoft Excel sheet from the following query

     

     

    SELECT TABLE_CATALOG, TABLE_NAME as tablename,COLUMN_NAME as columnname, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, *

     

    FROM INFORMATION_SCHEMA.COLUMNS

     

    WHERE TABLE_CATALOG = '<ENTER GoldMine database NAME>' order by tablename,COLUMNNAME

     

     

    This leads to a  query result similar to the following from a demo database but this  time ‘skips’ the DATADICT definition which was used by GoldMine when  requesting the blank structural database

     

     

     

    >> With this investigation only the column name, datatype and character maximum length is of interest (but still get the whole result as there might be something else to be investigated)

     

    3. Create a new Excel file with copy and paste table catalog, table name, column name, datatype and character maximum length information of both database schema side-by-side

    4. Make sure to adjust the matching table and column names on your sheet so that the rows are identical for table and column name

     

     

     

    5. Add 3 further columns to the following Excel formula =IF(C2=H2,1,0), adjusted with the specific columns, datatype, and length.

     

     

    6. Copy and paste the Excel formula for all rows

     

    7. Now look through and identify any 0 and the reason and if necessary follow-up action for the specific field (see additional note to make it more convenient to identify differences)

     

     

     

    A possible result might be then

     

     

    Additional note:

     

    You can also use the conditional formatting - for example
    1. Highlight all values from a comparison result column
    2. Tab Home >>  Conditional Formatting >>  Icon Sets >> Indicators >> for example Signal indicators or right (green), wrong (red), attention (yellow)
    3. Now look through and identify any 0 and the reason and if necessary follow-up action for the specific field