======================================================================
   COMPARING TABLE STRUCTURES
   ======================================================================
   PRODUCT   :  R:BASE                  VERSION      :  3.1B or Higher
   CATEGORY  :  DATABASE DESIGN         SUBCATEGORY  :  STRUCTURE  
   ======================================================================
 
 
   Often you'll find that you want to compare the columns in two tables.  
   Mismatched columns can cause your applications to operate incorrectly.  
   For example, your forms will not work correctly if you have unexpected 
   common columns (columns that have the same name) in your tables or if 
   columns that are supposed to be common aren't. The error message "No 
   editable data in this table" is almost always the result of common 
   columns that weren't meant to link the tables together but that Forms 
   made into linking columns because they have the same name.
 
   Comparing the structures of two small tables is easy, but what do you 
   do when one of the tables has 33 columns and the other has 47?  This 
   program makes comparing the column structure of two tables easy, 
   regardless of table size.  It loads the structures into temporary 
   tables, and then compares the tables using the relational command 
   INTERSECT to find the common columns, and SUBTRACT to find the unique 
   columns.  The columns are listed to the screen with the option to 
   print them.
 
   The program uses some features, such as Yes/No dialog boxes, that are 
   new to R:BASE 3.1B.  If you have an earlier version of R:BASE you'll 
   need to make changes to the lines marked with an *( 3.1B ) in the 
   following listing.  Those lines of code will only work with R:BASE 
   version 3.1B or later.  Lines containing features new to 3.1A are also 
   marked.
 
   CLS
   SET VAR vt1 TEXT,vt2 TEXT
   SET message off
   SET error messages off
   WRITE 'Choose the first table...'
   CHOOSE vt1 from #tables
   CLS
   WRITE ' '
   WRITE 'Table 1 is ', .vt1    *( 3.1A )
   WRITE ' '
   WRITE 'CHOOSE the second table...'
   CHOOSE vt2 from #tables
   CLS
   WRITE ' '
   WRITE ' '
   WRITE 'Table 1 is ', .vt1, '        Table 2 is ', .vt2   *( 3.1A )
   WRITE ' '
   WRITE 'Comparing the tables...........'
   WRITE ' '
   OUTPUT q_q  *(scratch file for table1 structure)
   LIST TABLE &vt1
   OUTPUT r_r  *(scratch file for table2 structure)
   LIST TABLE &vt2
   OUTPUT trashcan  *(scratch file for load error messages)
   DROP TABLE q_
   DROP TABLE r_
   DROP TABLE r_nq_
   CREATE TABLE q_ (colnum1 INT,remains TEXT 20)   
   *(scratch table for table1)
   CREATE TABLE r_ (colnum2 INT,remains TEXT 20)   
   *(scratch table for table2)
   LOAD q_ FROM q_q
   LOAD r_ FROM r_r
   DELETE q_q
   DELETE r_r
   OUTPUT screen
   DELETE trashcan
   WRITE ' '
   INTERSECT q_ with r_ FORM r_nq_
   COMPUTE t_ AS ROWS FROM r_nq_   *(check to see if common columns found)
   IF t_ >= 1 THEN
     WRITE 'Common columns between',.vt1, 'and',.vt2   *( 3.1A )
     WRITE ' '
     SET HEADINGS off
     SELECT remains FROM r_nq_
     PAUSE 2    *( 3.1B )
   ELSE
     WRITE ' '
     WRITE 'No common columns between',.vt1,'and',.vt2   *( 3.1A )
     WRITE ' '
     PAUSE 2    *( 3.1B )
   ENDIF
   DIALOG 'Print the results? ' vans vkey yes   *( 3.1B )
   WRITE ' '
   IF vans = 'Yes' THEN
     OUTPUT PRINTER
     SET LINES=0
     WRITE 'Common columns between',.vt1, 'and',.vt2    *( 3.1A )
     WRITE ' '
     SELECT remains FROM r_nq_
     OUTPUT SCREEN
     SET LINES = 20
   ENDIF
   DIALOG 'Do you want to see the unique columns in the tables?'+ 
    vans,vkey,no   *( 3.1B )
   CLS
   IF vans='yes' THEN
     SUBTRACT q_ FROM r_ FORM q_mr_
     WRITE 'Columns in',.vt2,'that are not in',.vt1   *( 3.1A )
     SET HEADINGS off
     WRITE ' '
     SELECT remains FROM q_mr_
     PAUSE 2  *( 3.1B )
     CLS
     SUBTRACT r_ FROM q_ FORM r_mq_
     WRITE 'Columns in',.vt1,'that are not in',.vt2   *( 3.1A )
     WRITE ' '
     SELECT remains FROM r_mq_
     PAUSE 2   *( 3.1B )
     DIALOG 'Print the results? ' vans vkey yes    *( 3.1B )
     WRITE ' '
     IF vans = 'Yes' THEN
       OUTPUT PRINTER
       SET LINES=0
       WRITE 'Columns in',.vt2,'that are not in',.vt1    *( 3.1A )
       WRITE ' '
       SELECT remains FROM q_mr_
       WRITE 'Columns in',.vt1,'that are not in',.vt2    *( 3.1A )
       WRITE ' '
       SELECT remains FROM r_mq_
       OUTPUT SCREEN
       SET LINES = 20
     ENDIF
   ENDIF
   DROP TABLE q_
   DROP TABLE r_
   DROP TABLE r_nq_
   DROP TABLE q_mr_
   DROP TABLE r_mq_
  
   CLEAR VAR vt1,vt2,vans,t_,vkey
   SET HEADINGS on
   SET message on
   SET error messages on
   RETURN