Linux

selecting required sql from script using shell scripting

admin
Welcome to Programming Tutorial official website. Today - we are going to cover how to solve / find the solution of this error selecting required sql from script using shell scripting on this date .

I have a sybase ddl file having ddl’s of many tables.. but the issue is along wth ddl there are many other things and unnecessary index creation script and comments. Is there any way to extract only ddl from this file. below is a small sample of file.

-------------------------------------------
-- DDL fpr table 'prod.dbo.tbl1'
--------------------------------------------
print '<<<< creating table prod.dbo.tbl1
go
setuser 'dbo'
go

create table tbl1 (id numeric (10,0),
                     etity     CHAR(2))

-------------------------------------------
-- DDL fpr table 'prod.dbo.tbl2'
--------------------------------------------
print '<<<< creating table prod.dbo.tbl1
go
setuser 'dbo'
go

create table tbl2 (id numeric (10,0),
                     etity     CHAR(2))
---------------------------------------------------

from this file i want only the create table ddl’s. is there any way to achieve it.. the output should only be DDL’s like below..

create table tbl1 id numeric (10,0),
                     etity     CHAR(2);
create table tbl2 id numeric (10,0),
                     etity     CHAR(2);

Answer

Assumptions:

  • only interested in create table commands (ie, not interested in other table related DDL like create index, RI/check constraints, default/rule bindings, cache configurations, grant/revoke, etc)
  • a create table block is terminated via blank line (no spaces), go (starting in column 1) or comment block (-- starting in column 1)

Sample input file:

$ cat ddl.sql
-------------------------------------------
-- DDL fpr table 'prod.dbo.tbl1'
--------------------------------------------
print '<<<< creating table prod.dbo.tbl1
go
setuser 'dbo'
go

create table tbl1 (id numeric (10,0),
                     etity     CHAR(2))

-------------------------------------------
-- DDL fpr table 'prod.dbo.tbl2'
--------------------------------------------
print '<<<< creating table prod.dbo.tbl1
go
setuser 'dbo'
go

create table tbl2 (id numeric (10,0),
                     etity     CHAR(2))
---------------------------------------------------

create table tbl3 (id numeric (10,0),
                     etity     CHAR(2))
go
---------------------------------------------------

One awk solution:

awk '
/create table/         { printme=1 }     # set our "print" flag when we see "create table"
/^$/ || /^go/ || /^--/ { if (printme)    # if in midst of printing "create table" then 
                            print ";"    # print ending ";" and then
                         printme=0       # clear "print" flag
                       }
printme' ddl.sql                         # if "print" flag is set (ie, "=1") then print current line

Or as a one-liner with comments removed:

awk '/create table/ {printme=1} /^$/ || /^go/ || /^--/ { if (printme) print ";";printme=0} printme' ddl.sql

Running the above against my ddl.sql generates:

create table tbl1 (id numeric (10,0),
                     etity     CHAR(2))
;
create table tbl2 (id numeric (10,0),
                     etity     CHAR(2))
;
create table tbl3 (id numeric (10,0),
                     etity     CHAR(2))
;

NOTE: If OP needs the ending ; on the end of the line it is possible to add a bit more code to the awk solution …