[Solved] Extracting information from DDL's

Dear Experts,

I need your help here. I have lot of teradata DDL's as follows, i want to extract field names , field attributes and NOT NULL information from DDL.Could you please help here.

Sample DDL:

CREATE MULTISET TABLE APS_CALL_IN_PICKUP_CANCELED ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      cnfrm_nbr CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      crte_dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      shpr_nbr DECIMAL(11,0) COMPRESS 0. ,
      src_sys_nm VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      crte_tm TIME(0),
      delt_flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
      rec_last_updt_tmstp TIMESTAMP(0),
      callr_nm VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      callr_ph_nbr VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      cpcs_com_desc VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
      star_com_desc VARCHAR(150) CHARACTER SET LATIN NOT CASESPECIFIC,
      batch_id_nm CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
PRIMARY INDEX ( cnfrm_nbr ,crte_dt );

Expected output:

cnfrm_nbr|CHAR(20)|NOT NULL
crte_dt|DATE|
shpr_nbr|DECIMAL(11,0)
src_sys_nm|VARCHAR(10)
crte_tm|TIME(0)
delt_flg|CHAR(1)
rec_last_updt_tmstp|TIMESTAMP(0)
callr_nm|VARCHAR(20)
callr_ph_nbr|VARCHAR(20) 
cpcs_com_desc|VARCHAR(255)
star_com_desc|VARCHAR(150)
batch_id_nm|CHAR(8)|NOT NULL

Here is one way of doing it.

Define an array with all Teradata supported data types, match and print:

awk '
        BEGIN {
                DT["BYTEINT"]
                DT["DECIMAL"]
                DT["FLOAT"]
                DT["INTEGER"]
                DT["SMALLINT"]
                DT["BYTE"]
                DT["CHAR"]
                DT["VARCHAR"]
                DT["DATE"]
                DT["TIME"]
                DT["TIMESTAMP"]
        }
        {
                T = $2
                sub ( /\(.*/, X, T )
                sub ( /,/, X, $2 )
                if ( T in DT )
                        print $1, $2 ( $0 ~ "NOT NULL" ? "|NOT NULL": "" )
        }
' OFS=\| file.sql

Thank a lot Yoda. This worked like a charm.