1

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);
Improve this question
1
  • See my edited answer for a sed script to do the extraction. Commented Oct 6, 2009 at 13:50

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Dennis Williamson. For the second script that was the problem. converted the file with dos2unix and then it worked fine.
@Dennis Willimson. The command works. +1 for providing improved solution. Thanks.
1

Untested:

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

Stop messing around with sed.

Improve this answer

5 Comments

@reinierpost. Tried this. getting same results which i am getting with SED. but if you copy the second sql code i pasted, doesnt work with that. any idea why?
@reinierpost. How to remove the extracted lines from the existing file? +1.
@Viky: it works for me. To get everything that doesn't match, add a ! after the if, or change the if to 'unless' if you prefer. To replace the input file with the resulting output, add -i.bak before the -e, the original will end up in all.sql.bak.
@reinierpost. Want the pattern to be removed from the original file without its name getting changed and the removed pattern to be updated in a file.
Do what I suggested. First without the ! and without the -i.bak, then with both. Then the removed lines will be in eferences.sql. If you need to do this kind of thing more often, some course or tutoring from an experienced scripter may be advisable.
0

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
Improve this answer

2 Comments

You can do for filename in *.sql the ls is not needed.
See my edited answer to do your sed/sed/mv in one line without the mv.
0

My initial thought is to do it in two steps:

1. use grep to pull the pattern out and put it in a separate file

2. use sed to delete it from the original file

but there might well be a better way.

grep -i "alter.*foreign key.*[\/|;]" filename.txt > alter_statements.txt

sed  -e "/alter.*foreign key.*[\/|;]/d" filename.txt > everything_else.txt

This would only work for alter statements on single lines, though.

[edited to correct formatting]

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.