text processing to modify sql create script

pcock · 03-16-2009, 07:59 PM

I have a large number of sql scripts that I'd like to process.

Code:

CREATE TABLE WAREHOUSE_OBJECT
(
  OBJECT_ID          numeric(15)   NOT NULL,
  OBJECT_IDENTIFIER  char(32)      NOT NULL
);

CREATE UNIQUE INDEX  S_WAREHOUSE_OBJECT_PK ON  S_WAREHOUSE_OBJECT
(CONFIG_ID);

ALTER TABLE  S_WAREHOUSE_OBJECT ADD(  --remove open bracket here
  CONSTRAINT S_WAREHOUSE_OBJECT_PK
 PRIMARY KEY
 (CONFIG_ID));  -- remove last close bracket here

I'd like to remove open/closed brackets (at ALTER TABLE) so it looks like this.

Code:

CREATE TABLE WAREHOUSE_OBJECT
(
  OBJECT_ID          numeric(15)   NOT NULL,
  OBJECT_IDENTIFIER  char(32)      NOT NULL
);

CREATE UNIQUE INDEX  S_WAREHOUSE_OBJECT_PK ON  S_WAREHOUSE_OBJECT
(CONFIG_ID);

ALTER TABLE  S_WAREHOUSE_OBJECT ADD 
  CONSTRAINT S_WAREHOUSE_OBJECT_PK
 PRIMARY KEY
 (CONFIG_ID);

I tried something like this in awk but not sure how to eliminate the brackets

Code:

BEGIN {OFS=" "}
  $1 =="ALTER"      { x=$0 }
  $1 =="CONSTRAINT" { y=$0}
  $1 =="PRIMARY"    { z=$0}
  $1 =="((" {print x, y, z, $0}

pcock · 03-17-2009, 04:42 AM

I found a way to fix this

sed 's/ADD(/ADD/'
sed 's/));/);/'