LINUX:如何输出 SQL 脚本中使用的表

Question

人。

我有大量的 SQL 脚本(1000 多个文件)，我必须列出有关它们的一些信息。主要问题是在每个脚本上列出所有表和正在使用的操作。我主要需要查找 SELECT、INSERT、DELETE 和 UPDATE 操作。

选择示例:

     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;

SELECT 的输出将是(顺序无关紧要):

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 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;

插入的输出将是(顺序无关紧要):

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 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';

删除的输出将是(顺序无关紧要):

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 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;

更新的输出将是(顺序无关紧要):

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)

关于哪个实用程序最适合做这件事的任何线索，如何克服主要问题，比如 FROM 之后的换行符，比如如何在一个特定的工作之后获取单词(比如如何获取 FROM 子句之后的内容)，如何确定 JOIN 子句等的主要操作将是最受欢迎的。

谢谢大家

Answer 1

嗯，将所有 SQL 语法(PostgeSQL、Oracle、MS ..)解释为正则表达式非常困难。

无论如何，这是 AWK 的一些初始示例，它应该适用于您的 SELECT 和 INSERT 语句示例:

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

您可以继续并调整以获得更多更可靠的代码。

注意:根据您的示例，我简化了任务并假设您的表始终采用 schema.table 形式。

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

INSERT 的输出:

SCHEMA1.TABLE_A | INSERT
SCHEMA1.TABLE_B | INSERT
SCHEMA2.TABLE_C | INSERT
SCHEMA5.TABLE_C | SELECT