LINUX: How to output tables used in SQL script












1















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.










share|improve this question



























    1















    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.










    share|improve this question

























      1












      1








      1








      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.










      share|improve this question














      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.







      linux shell unix awk grep






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 23 '18 at 14:16









      Bernardo PinaBernardo Pina

      62




      62
























          2 Answers
          2






          active

          oldest

          votes


















          0














          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





          share|improve this answer































            0














            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





            share|improve this answer

























              Your Answer






              StackExchange.ifUsing("editor", function () {
              StackExchange.using("externalEditor", function () {
              StackExchange.using("snippets", function () {
              StackExchange.snippets.init();
              });
              });
              }, "code-snippets");

              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "1"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53448320%2flinux-how-to-output-tables-used-in-sql-script%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              0














              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





              share|improve this answer




























                0














                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





                share|improve this answer


























                  0












                  0








                  0







                  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





                  share|improve this answer













                  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






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 23 '18 at 16:28









                  KubatorKubator

                  74911




                  74911

























                      0














                      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





                      share|improve this answer






























                        0














                        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





                        share|improve this answer




























                          0












                          0








                          0







                          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





                          share|improve this answer















                          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






                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Nov 23 '18 at 16:53

























                          answered Nov 23 '18 at 16:42









                          mah454mah454

                          499415




                          499415






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Stack Overflow!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53448320%2flinux-how-to-output-tables-used-in-sql-script%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              404 Error Contact Form 7 ajax form submitting

                              How to know if a Active Directory user can login interactively

                              TypeError: fit_transform() missing 1 required positional argument: 'X'