Cull Unused DB Table Fields

This example demonstrates the technique of returning only those items in a formatted list that are contained in a simple list of names. For example, suppose you have a database table's structure formatted as field name, type, size and description:

CUSTOMER      String       30       Company 
END_DATE      String       16       Job end date 
COUNTRY       String       30       Country 
CO_REP1       String       15       Company Rep. 1 
FIELD         String       30       Field 
SS_FAX        String       30       SSDS office FAX 
REGION        String       15       SSDS Region 
ST_DATE       String       16       Job start date 

Now suppose the table structure is rather lengthy, containing 200 or more fields making it impossible to handle without some sort of automation. Of those 200 fields, only about 50 are actually used and by whatever means, you happen to have a list of all 50 or so used fields.  For sake of demonstration we'll represent this set of used fields by the following short list of field names:

CUSTOMER
CO_REP1
FIELD
REGION

Now, say you'd like to reduce the formatted list so that it contains only the 50 or so fields which are actually in-use. How do you do this?  Simple.  Since you have a list of the 50 or so fields that are in actual use, you merely combine both lists, sort them together and using the OutDuplLines filter, you output all duplicates according to the field names only. Then, you get rid of the lines containing only the field names and you're done!  Pretty simple using English as a programming language, huh?  Fortunately it's not much more difficult using TEXTools.  Again, starting with a combined list, we sort it using the SortLines filter:

SortLines

Executing this one-filter pipe gives us:

CO_REP1
CO_REP1       String       15       Company Rep. 1 
COUNTRY       String       30       Country 
CUSTOMER
CUSTOMER      String       30       Company 
END_DATE      String       16       Job end date 
FIELD
FIELD         String       30       Field 
REGION
REGION        String       15       SSDS Region 
SS_FAX        String       30       SSDS office FAX 
ST_DATE       String       16       Job start date 

Next, we add two more filters to the pipe: the PadLinesRight filter to pad the lines to a minimum width of 14 characters and the OutDuplLines filter to output only those lines that are duplicated according to character columns 1 to 14:

SortLines
PadLinesRight ' ' /w14
OutDuplLines 1 14

The pipe's execution now gives us the following output:

CO_REP1       
CO_REP1       String       15       Company Rep. 1 
CUSTOMER      
CUSTOMER      String       30       Company 
FIELD         
FIELD         String       30       Field 
REGION        
REGION        String       15       SSDS Region 

Now, to get rid of the lines with only a field name, we add the JoinLines filter to join every two lines onto a single line:

SortLines
PadLinesRight ' ' /w14
OutDuplLines 1 14
JoinLines 2

This results in the following:

CO_REP1       CO_REP1       String       15       Company Rep. 1 
CUSTOMER      CUSTOMER      String       30       Company 
FIELD         FIELD         String       30       Field 
REGION        REGION        String       15       SSDS Region 
----+----1----+----2----+----3----+----4----+----5----+----6----+

Finally, we add the DelChars filter to our pipe to delete the first 14 characters thereby getting rid of the redundant field names:

SortLines
PadLinesRight ' ' /w14
OutDuplLines 1 14
JoinLines 2
DelChars 1 14

Execution of this pipe results in the desired output:

CO_REP1       String       15       Company Rep. 1 
CUSTOMER      String       30       Company 
FIELD         String       30       Field 
REGION        String       15       SSDS Region 
----+----1----+----2----+----3----+----4----+----5----+----6----+

An alternative approach to getting rid of the redundant field names is demonstrated by the following pipe which pads the lines with asterisks (*) to character column 15 and then removes those lines with an asterisk in column 15: 

SortLines
PadLinesRight ' ' /w14
OutDuplLines 1 14
PadLinesRight '*' /w15
ExclLines '*' 15 15

[Home] [Contact Us] [Downloads] [Purchase/Register]

Copyright © 2005 Firefly Software