Finding a pattern and extracting it into another file using sed in BASH

Question

I have a few SQL scripts which contains table creation SQL statements. I want to remove all the statements having foreign key mappings.

For example below is one script.

CREATE TABLE x (
    ORG_ID  NUMBER primary key,
    BILLING_doc xmltype
);

alter table x
 add (constraint fk_x foreign key
  (org_id) references organization\r
  (org_id))
/

I want to remove the alter table statement referencing the foreign key statement and put it in another file. As you see the statement ends with a /. Some files end with a semicolon.

I want to do this using sed. Note that the alter statement shown here is spread across lines and is not on a single line.

Any help is appreciated.

While working I found the following but it is not working for all scripts:

sed -e '/./{H;$!d;}' -e 'x;/foreign/!d;' myfile.sql

Script where the above command is not working:

create table io_template
(
    io_template_id number,
    io_template_name varchar2(50),
    acl_id number,
    org_id number,
    is_active varchar2(1),
    xsl_template varchar2(50),
    email_body varchar2(2000),
    purpose varchar2(50) default 'CUSTOMER' not null,
    content_type varchar2(50) default 'PDF' not null
);

alter table _io_template add constraint io_template_pk
      primary key (io_template_id);

alter table _o_template add constraint io_template_acl_fk
      foreign key (acl_id) references scum_acl(acl_id);

alter table io_template add constraint io_template_org_fk
      foreign key (org_id) references scum_organization(org_id);

alter table io_template add constraint io_template_name_uk
      unique (org_id, _io_template_name);

Answer 1

Untested:

perl -e '$/=""; while(<>) { print if / references /; }' all.sql > references.sql

Stop messing around with sed.

Answer 2

You don't say exactly how the sed script is not working on the second SQL script, but I'm going to assume that it prints all lines.

When I run it against both SQL scripts it seems to work correctly. Only the alter table statements with foreign keys are output.

The first clue for me to what might be wrong is that the format of the two SQL scripts is different. There's the capitalization, the different layout, the slash versus semicolon, plus the possible stray "\r".

So I ran unix2dos on the second SQL script and ran the sed script again. This time it output all lines.

So, to fix your problem, try doing the reverse:

dos2unix sqlscriptname

Edit:

In order to modify the original SQL file in place and save the alter table statements with foreign keys to a file called foreign.out you can do this:

 sed -i.bak  -e '/./{H;$!d;}' -e 'x;/foreign/w foreign.out' -e '/foreign/d' sqlscriptname

This leaves the original SQL script in place, but with the statements removed, backs up the unchanged script to sqlscriptname.bak and writes the statements to foreign.out.

Answer 3

The below script did the trick for me

#!/bin/bash
for filename in `ls *.sql`
do
dos2unix $filename
sed -e '/./{H;$!d;}' -e 'x;/[fF][oO][rR][eE][iI][gG][nN]/!d;' $filename >> fk_file.sql
sed -e '/./{H;$!d;}' -e 'x;/[fF][oO][rR][eE][iI][gG][nN]/d' $filename > $filename.new
mv $filename.new $filename
done