Hi,
I have a file say with following lines (the lines could start from any column and there can be many many create statements in the file)
create table table1....table definition...
insert into table1 values.....
create or replace view view1....view definition....
What i want is to extract the object type and object name being created i.e. keyword 'table' and table name (table1) and 'view' keyword and view name (view1)? What (grep/sed/awk) and How do I use it to extract this data?
Is there any way to store all the object types and object names in an array sort of thing?
Experts please guide me as to how i can do this in my shell script....
For lines beginning with "create table", print column 3. For lines beginning with "create or replace view", print column 5. All other lines get ignored.
awk '/^create table/ { print $3 }; /^create or replace view/ { print $5 }' datafile
Do you actually need to store them all, or just use them one at a time? It's a bad idea to try and store endless amounts of data in a shell because there are limits, sometimes severe limits, on the amount of data you can store in one variable.
awk '/^create table/ { print $3 }; /^create or replace view/ { print $5 }' datafile |
while read LINE
do
echo "got name $LINE"
done
1 Like
Thanks Corona it works!!!
I want to make my program foolproof so I want to consider scenarios where data might not be in good format always...Please advise on how to handle below scenarios...
-
what if the name of the table or view is on the second line
create table
table1 (c1 integer)
-
What if there are multiple spaces between create, table and tablename
create table table1
I have taken care of the key words being in mixed case by using toupper function.