Deleting unused fields from table

[sermac]

Hi:
Is there a way(.prg) to delete "un-used" fields ("not records&quot from each or all of the tables in the project or a way to programtically determine which table fields are not being used thru out the project and all the various forms?
(. . .comes from pp planning. . .and poor memory...and learning as you go! almost to embarrassing to ask this but I will anyway.)

THX for any feedback. . .all you are great "teachers"
. . .watching the permutation thread "78 responses" amazing
chalk talk!

Bill from Denver

 

[EzLogic]

Alter table YourTableName Drop Column TheFieldYouwishtoDrop



Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan

 

[DSummZZZ]

There's not an efficient way to determine unused fields in a project programmatically, due to macro substitution and alias usually being scattered around. You will probably have to drop them and (from backups of course) and take the chance. Drop one at a time and test it good though.
Anyway, to programmatically drop a field, you can use something like:

USE MyTable EXCLUSIVE
ALTER TABLE MyTable DROP SomeField


Dave S.

 

[baltman]

Bill,

Don't drop one at a time if you have multiple fields to drop. The table is copied each time. Drop/Alter/Add all columns at one time for the quickest results.

Brian

&&&&&&&&& START CODE &&&&&&&&&
ALTER TABLE MyTable ;
DROP SomeField1 ;
DROP SomeField2 ;
ADD SomeOtherField c(10) ;
ALTER COLUMN YetAnother n(5,2) ;
RENAME COLUMN LastOne to LastOne2
&&&&&&&&& END CODE &&&&&&&&&

I don't know what you mean by programatically dropping unused columns, but if you mean determining which fields are unused use a FOR..ENDFOR loop with AFIELDS() and devise a test for character and/or numerical fields that suits what you expect in the data. Below assumes a MAXIMUM of ZERO
means that a numerical field is not used.

&&&&&&&&& START CODE &&&&&&&&&
TargetTable="MyTable"
CmdString=""

Close All
Use &TargetTable

FOR X = 1 TO AFIELDS(TempArray)
VarField=TempArray(X,1)
calc max(&VarField) to TestVal

if type(VarField)="N"
if TestVal=0
CmdString=CmdString+" DROP COLUMN "+VarField
endif
endif

if type(VarField)="C"
if LEN(ALLTRIM(TestVal))=0
CmdString=CmdString+" DROP COLUMN "+VarField
endif
endif
ENDFOR

IF LEN(CmdString)>0
ALTER TABLE &TargetTable &CmdString
ENDIF
&&&&&&&&& END CODE &&&&&&&&&