Quick way of create new fields?

[JamesBowen]

Is there a quick and dirty way of create new fields to every table in a database?

I need to add four new fields to every table in my database. I have about 30 tables and I don't fancy manually adding four new fields to each table.

 

[sphipp]

The easiest way to do this is to create a .df file to add the fields.

Try something like this to generate a DF then apply the DF to a copy of the database to check that it works.

If you want an index on the table you will need to add the index fields as well.

Obviously, you create a ttfield record for each of the extra common fields you want to create and set the labels, types and so on according to which fields you want to add.

DEF TEMP-TABLE ttfield NO-UNDO LIKE _field
  FIELD tablename AS CHAR.
DEFINE VARIABLE iPos AS INTEGER    NO-UNDO.
DEFINE VARIABLE iOrder AS INTEGER    NO-UNDO.
DEFINE VARIABLE lInclude AS LOGICAL NO-UNDO.
 
FOR EACH _file WHERE _file._tbl-type = "T" NO-LOCK BREAK BY _file._file-name:
    IF _file._file-name BEGINS "_" THEN NEXT.
    ASSIGN lInclude = YES.
    DISPLAY _file._file-name WITH FRAME fr_include SIDE-LABELS 1 DOWN.
    UPDATE lInclude LABEL "Add Fields to Table?" WITH FRAME fr_include.
    IF lInclude = NO THEN NEXT.
    ASSIGN ipos = 0
           iorder = 0.
    FOR EACH _field OF _file NO-LOCK:
         ASSIGN ipos = MAX(ipos,_field._field-rpos)
                iorder = MAX (iorder,_field._order).
    END.
    ASSIGN ipos = ipos + 1
           iorder = iorder + 10.
 
    CREATE ttfield.
    ASSIGN ttfield.tablename   = _file._file-name
           ttfield._order      = iorder
           ttfield._field-rpos = ipos
           ttfield._field-name = "Extra1"
           ttfield._data-type = "character"
           ttfield._format = "X(30)"
           ttfield._initial = "Whatever you want"
           ttfield._label = "Extra 1"
           ttfield._col-label = "Extra!1"
           ttfield._width = 10
        .
    ASSIGN ipos = ipos + 1
       iorder = iorder + 10.
    CREATE ttfield.
    ASSIGN ttfield.tablename   = _file._file-name
           ttfield._order      = iorder
           ttfield._field-rpos = ipos
           ttfield._field-name = "Extra2"
           ttfield._data-type = "date"
           ttfield._format = "99/99/9999"
           ttfield._initial = "?"
           ttfield._label = "Extra 2"
           ttfield._col-label = "Extra!2"
           ttfield._width = 4
        .
 
    ASSIGN ipos = ipos + 1
       iorder = iorder + 10.
    CREATE ttfield.
    ASSIGN ttfield.tablename   = _file._file-name
           ttfield._order      = iorder
           ttfield._field-rpos = ipos
           ttfield._field-name = "Extra3"
           ttfield._data-type = "INTEGER"
           ttfield._format = ">,>>>,>>9-"
           ttfield._initial = "0"
           ttfield._label = "Extra 3"
           ttfield._col-label = "Extra!3"
           ttfield._width = 4
        .
    ASSIGN ipos = ipos + 1
       iorder = iorder + 10.
    CREATE ttfield.
    ASSIGN ttfield.tablename   = _file._file-name
           ttfield._order      = iorder
           ttfield._field-rpos = ipos
           ttfield._field-name = "Extra4"
           ttfield._data-type = "decimal"
           ttfield._format = "->>,>>9.99"
           ttfield._initial = "10"
           ttfield._label = "Extra 1"
           ttfield._col-label = "Extra!1"
           ttfield._width = 17
           ttfield._decimals = 2
           ttfield._extent = 3
        .
 
    ASSIGN ipos = ipos + 1
       iorder = iorder + 10.
    CREATE ttfield.
    ASSIGN ttfield.tablename   = _file._file-name
           ttfield._order      = iorder
           ttfield._field-rpos = ipos
           ttfield._field-name = "Extra5"
           ttfield._data-type = "LOGICAL"
           ttfield._format = "yes/no"
           ttfield._initial = "yes"
           ttfield._label = "Extra 5"
           ttfield._col-label = "Extra!5"
           ttfield._width = 1
        .
END.
OUTPUT TO dbdelta.df.
PUT UNFORMATTED "UPDATE DATABASE ""?""" SKIP(1).
FOR EACH ttfield BREAK BY ttfield.tablename BY ttfield._order:
PUT UNFORMATTED
    "ADD FIELD """ ttfield._field-name """ OF """ ttfield.tablename """ AS " ttfield._data-type SKIP
    "  FORMAT """ ttfield._format """" SKIP
    "  INITIAL """ ttfield._initial """" SKIP
    "  LABEL """ ttfield._label """" SKIP
    "  POSITION " ttfield._field-rpos SKIP
    "  SQL-WIDTH " ttfield._width SKIP.
IF ttfield._col-label <> "" THEN PUT UNFORMATTED "  COLUMN-LABEL """ ttfield._col-label """" SKIP.
IF ttfield._decimals > 0 THEN PUT UNFORMATTED "  DECIMALS " ttfield._decimals SKIP.
PUT UNFORMATTED   "  ORDER " SKIP(1).
END.
OUTPUT CLOSE.

 

[JamesBowen]

Thanks. I have not tried this code yet. I will test it first thing tomorrow morning when I get into work. At first glance it looks good and makes perfect sense.