Argthtjtr

people.

I have a ton of SQL scripts (1000+ files), and I have to list some information about them. The main problem is to list all tables, and operations being used, on each script. I need to find mainly SELECT, INSERT, DELETE and UPDATE operations.

SELECT EXAMPLE:

     SELECT column1, column2

       FROM SCHEMA1.TABLE_A A, SCHEMA1.TABLE_B B,

            SCHEMA3.TABLE_K, SCHEMA3.TABLE_M,

            SCHEMA4.TABLE N

      INNER JOIN SCHEMA2.TABLE_C C ON C.COLUMN_XX = A.COLUMN_XX

       LEFT JOIN SCHEMA2.TABLE_D D ON D.COLUMN_YY = A.COLUMN_YY

 FULL OUTER JOIN SCHEMA2.TABLE_E E ON E.COLUMN_ZZ = B.COLUMN_ZZ

      WHERE B.COLUMN_AA = A.COLUMN_AA

        AND K.COLUMN_KK = A.COLUMN_KK

        AND M.COLUMN_MM = A.COLUMN_MM

        AND N.COLUMN_NN = A.COLUMN_NN;

OUTPUT FOR SELECT WOULD BE (ORDER DOESNT MATTER):

SCHEMA1.TABLE_A | SELECT

SCHEMA1.TABLE_B | SELECT

SCHEMA3.TABLE_K | SELECT

SCHEMA3.TABLE_M | SELECT

SCHEMA4.TABLE_N | SELECT

SCHEMA2.TABLE_C | SELECT

SCHEMA2.TABLE_D | SELECT

SCHEMA2.TABLE_E | SELECT

INSERT EXAMPLE:

INSERT INTO SCHEMA1.TABLE_A (COLUMN1,COLUMN2) VALUES ('VALUE_1','VALUE_2');

INSERT INTO SCHEMA1.TABLE_B

(COLUMN_1,COLUMN2)

VALUES

('VALUE_1','VALUE_2');

INSERT INTO SCHEMA2.TABLE_C SELECT * FROM SCHEMA5.TABLE_C;

OUTPUT FOR INSERT WOULD BE (ORDER DOESNT MATTER):

SCHEMA1.TABLE_A | INSERT

SCHEMA1.TABLE_B | INSERT

SCHEMA2.TABLE_C | INSERT

SCHEMA5.TABLE_C | SELECT (IF IT IS "OK" TO PUT SOMETHING LIKE 'SELECT (INSERT)' WOULD BE BETTER.. IF NOT, THAT'S OK)

DELETE EXAMPLE:

DELETE FROM SCHEMA3.TABLE_K WHERE K.COLUMN_KK = 'A';

DELETE FROM SCHEMA1.TABLE_A A

 INNER JOIN SCHEMA2.TABLE_C C ON C.COLUMN_XX = A.COLUMN_XX

 WHERE A.COLUMN_AA = 'A';

OUTPUT FOR DELETE WOULD BE (ORDER DOESNT MATTER):

SCHEMA3.TABLE_K | DELETE

SCHEMA1.TABLE_A | DELETE

SCHEMA2.TABLE_C | JOIN (IF IT IS "OK" TO PUT SOMETHING LIKE 'JOIN (DELETE)' WOULD BE BETTER... IF NOT, THAT'S OK)

UPDATE EXAMPLE:

UPDATE SCHEMA1.TABLE_A SET COLUMN_AA = 'A';

UPDATE SCHEMA2.TABLE_C SET COLUMN_CC = (SELECT COLUMN_CC FROM SCHEMA5.TABLE_C)

 WHERE EXISTS (SELECT * FROM SCHEMA5.TABLE_C C2 WHERE C2.COLUMN_CC = C.COLUMN_CC);

UPDATE SCHEMA2.TABLE_D D

 INNER JOIN SCHEMA1.TABLE_A A ON D.COLUMN_ID = A.COLUMN_ID

   SET D.COLUMN_DD = A.COLUMN_DD;

OUTPUT FOR UPDATE WOULD BE (ORDER DOESNT MATTER):

SCHEMA1.TABLE_A | UPDATE

SCHEMA2.TABLE_C | UPDATE

SCHEMA5.TABLE_C | SELECT

SCHEMA2.TABLE_D | UPDATE

SCHEMA1.TABLE_A | JOIN (IF IT IS "OK" TO PUT SOMETHING LIKE 'JOIN (UPDATE)' WOULD BE BETTER... IF NOT, THAT'S OK)

Any clues about which utility is the best to do it, how to overcome main problems, like line breaks after FROM, like how to get words after one specific work (like how to get what is after FROM clause), how to identify the main operation on the JOIN clauses, etc, would be most welcome.

Thank you ALL.

Hmm this is quite tough to interpret all SQL syntax (PostgeSQL, Oracle, MS ..,) into regexps.

Anyway here is some initial example of AWK which should work for Your SELECT and INSERT statements examples:

awk '

BEGIN { RS="[ ,;n]"; }

/^SELECT$|^INSERT$/ { operation=$1; watchtable=0; }

/^FROM$|^JOIN$|^INTO$/ { watchtable=1; }

/^WHERE$|^INNER$|^OUTER$|^LEFT$|^RIGHT$|^ON$|^ORDER$|^GROUP$|^(/ { watchtable=0; }

watchtable && /^[^.]+.[^.]+$/ { print($0 " | " operation); }

' SELECT.sql INSERT.sql

You can continue and tune up for further and more solid code.

Note: Per Your examples I made the task easier and assume that Your Tables are always in form schema.table.

Output for SELECT:

SCHEMA1.TABLE_A | SELECT

SCHEMA1.TABLE_B | SELECT

SCHEMA3.TABLE_K | SELECT

SCHEMA3.TABLE_M | SELECT

SCHEMA4.TABLE | SELECT

SCHEMA2.TABLE_C | SELECT

SCHEMA2.TABLE_D | SELECT

SCHEMA2.TABLE_E | SELECT

Output for INSERT:

SCHEMA1.TABLE_A | INSERT

SCHEMA1.TABLE_B | INSERT

SCHEMA2.TABLE_C | INSERT

SCHEMA5.TABLE_C | SELECT

simple command for all operations :

cat your_sql_script.sql | tr -d "n" | tr ";" "n" | sed 's/$/;/'

Now you can grep any sql command .

this is output for your delete pasted :

DELETE FROM SCHEMA3.TABLE_K WHERE K.COLUMN_KK = 'A'

DELETE FROM SCHEMA1.TABLE_A A INNER JOIN SCHEMA2.TABLE_C C ON C.COLUMN_XX = A.COLUMN_XX WHERE A.COLUMN_AA = 'A'     

Maybe this help you :

cat SELECT.sql | tr -d "n" | tr ";" "n" | sed 's/$/;/' | awk '{print $3,"|",$1}'     

output :

SCHEMA3.TABLE_K | DELETE

SCHEMA1.TABLE_A | DELETE