Get specific line below grepped line

Question

I have a challenging issue that I am facing below

I have the below text in a file:

create table "ctronsys".activity_codes
(
   code_list_id varchar(8),
   code_id varchar(10),
   record_type varchar(1),
   active_status_ind varchar(1),
   security_level varchar(1),
   short_description varchar(10),
   long_description varchar(30),
   record_class varchar(1),
   owner_id varchar(4),
   modify_user_id varchar(3),
   modify_date date,
   create_user_id varchar(3),
   create_date date
) in datadbs extent size 16 next size 16 lock mode row;

My aim is to grep for the below line in the text file:

create table "ctronsys".activity_codes

and using the above to retrieve the line below:

) in datadbs extent size 16 next size 16 lock mode row;

Note that the number of lines to search below is not specific according to table structures

I have the below command, which allows me to print a column within a line above the grepped line using awk, however this only prints a single column within that line whereas I would like to print the entire line:

cat table_structure | awk -v key="datadbs" '/^create/ {val=$3} $3 == key {print val;}'

Answer 1

Using awk:

awk 'BEGIN { FS="\n[()]"; RS=";" } /^create table "ctronsys".activity_codes/ { print $NF }' file
 in datadbs extent size 16 next size 16 lock mode row

We divide the file contents into ;-separated records. Each record is divided into fields based on the ( or ) at the beginning of a line.

When reading the file, the first record will be the full contents of this particular file.

The first field of that record is

create table "ctronsys".activity_codes

The second field is

   code_list_id varchar(8),
   code_id varchar(10),
   record_type varchar(1),
   active_status_ind varchar(1),
   security_level varchar(1),
   short_description varchar(10),
   long_description varchar(30),
   record_class varchar(1),
   owner_id varchar(4),
   modify_user_id varchar(3),
   modify_date date,
   create_user_id varchar(3),
   create_date date

And the last field is

 in datadbs extent size 16 next size 16 lock mode row