Ugrás a fő tartalomra

Adatbázis kutakodás

 Adatbázis kutakodás



Oracle DB

Ez a lekérdezés oracle adatbázis esetén kilistázza azokat az objektumokat, amelyek forráskódjában mindkét megadott string (objektumnév) szerepel, függetlenül attól, hogy melyik sorban.

---1

SELECT DISTINCT s.OWNER, s.NAME, s.TYPE
FROM ALL_SOURCE s
WHERE s.TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'VIEW')
AND s.TEXT LIKE '%MY_PROCEDURE%'
AND s.NAME IN (
    SELECT s2.NAME
    FROM ALL_SOURCE s2
    WHERE s2.OWNER = s.OWNER
    AND s2.TYPE = s.TYPE
    AND s2.TEXT LIKE '%MY_TABLE%'
);


---2. másik megközelítés

SELECT DISTINCT a.name, a.type
FROM all_source a
WHERE a.name IN (
    SELECT name
    FROM all_source
    WHERE UPPER(text) LIKE '%CUSTOMERS%'
)
AND a.name IN (
    SELECT name
    FROM all_source
    WHERE UPPER(text) LIKE '%ORDERS%'
);


---3. jobb verzio

SELECT name, type
FROM (
    SELECT name, type,
           LISTAGG(UPPER(text), ' ') WITHIN GROUP (ORDER BY line) AS full_source
    FROM all_source
    GROUP BY name, type
)
WHERE full_source LIKE '%CUSTOMERS%'
  AND full_source LIKE '%ORDERS%';


----4. Paraméterezhető

DEFINE obj1 = 'CUSTOMERS'
DEFINE obj2 = 'ORDERS'

SELECT name, type
FROM (
    SELECT name, type,
           LISTAGG(UPPER(text), ' ') WITHIN GROUP (ORDER BY line) AS full_source
    FROM all_source
    GROUP BY name, type
)
WHERE full_source LIKE '%' || UPPER('&obj1') || '%'
  AND full_source LIKE '%' || UPPER('&obj2') || '%';


5.  gyorsabb

SELECT name, type
FROM user_source
WHERE UPPER(text) LIKE '%CUSTOMERS%'
INTERSECT
SELECT name, type
FROM user_source
WHERE UPPER(text) LIKE '%ORDERS%';

6. leggyorsabb

SELECT name, type
FROM (
    SELECT name, type
    FROM user_source
    WHERE INSTR(UPPER(text), 'CUSTOMERS') > 0
)
INTERSECT
SELECT name, type
FROM (
    SELECT name, type
    FROM user_source
    WHERE INSTR(UPPER(text), 'ORDERS') > 0
);



--- Tábla nevek kigyüjtése 
SELECT name, type, line,
       REGEXP_SUBSTR(text, '(FROM|JOIN|INTO|UPDATE)\s+([A-Z0-9_]+)', 1, 1, NULL, 2) AS table_name
FROM user_source
WHERE REGEXP_LIKE(text, '(FROM|JOIN|INTO|UPDATE)\s+[A-Z0-9_]+', 'i');


---- komplex kigyüjtő script

DECLARE
    CURSOR src_cur IS
        SELECT name, type, line, text
        FROM user_source
        WHERE UPPER(text) LIKE '%SELECT%'
           OR UPPER(text) LIKE '%FROM%'
           OR UPPER(text) LIKE '%JOIN%'
           OR UPPER(text) LIKE '%WHERE%'
           OR UPPER(text) LIKE '%UPDATE%'
           OR UPPER(text) LIKE '%INSERT%'
           OR UPPER(text) LIKE '%INTO%';

    v_line         VARCHAR2(4000);
    v_table_name   VARCHAR2(128);
    v_column_name  VARCHAR2(128);
    v_context      VARCHAR2(30);
BEGIN
    FOR r IN src_cur LOOP
        v_line := UPPER(r.text);

        -- Táblanév FROM után
        IF REGEXP_LIKE(v_line, '\bFROM\b\s+[A-Z0-9_]+') THEN
            v_table_name := REGEXP_SUBSTR(v_line, '\bFROM\b\s+([A-Z0-9_]+)', 1, 1, NULL, 1);
            INSERT INTO plsql_table_column_usage
            VALUES (r.name, r.type, r.line, v_table_name, NULL, 'FROM');
        END IF;

        -- Táblanév JOIN után
        IF REGEXP_LIKE(v_line, '\bJOIN\b\s+[A-Z0-9_]+') THEN
            v_table_name := REGEXP_SUBSTR(v_line, '\bJOIN\b\s+([A-Z0-9_]+)', 1, 1, NULL, 1);
            INSERT INTO plsql_table_column_usage
            VALUES (r.name, r.type, r.line, v_table_name, NULL, 'JOIN');
        END IF;

        -- Oszlopnevek SELECT-ben (egyszerű esetek: SELECT col1, col2, ...)
        IF REGEXP_LIKE(v_line, '\bSELECT\b\s+') THEN
            FOR col IN (
                SELECT REGEXP_SUBSTR(trim_col, '[A-Z0-9_\.]+', 1, 1) AS col_name
                FROM (
                    SELECT REGEXP_SUBSTR(v_line, 'SELECT\s+(.*)\s+FROM', 1, 1, NULL, 1) AS col_block
                    FROM dual
                ) sub,
                TABLE (
                    CAST (
                        MULTISET (
                            SELECT trim(regexp_substr(col_block, '[^,]+', 1, LEVEL)) AS trim_col
                            FROM dual
                            CONNECT BY LEVEL <= REGEXP_COUNT(col_block, ',') + 1
                        ) AS SYS.ODCIVARCHAR2LIST
                    )
                )
            ) LOOP
                INSERT INTO plsql_table_column_usage
                VALUES (r.name, r.type, r.line, NULL, col.col_name, 'SELECT');
            END LOOP;
        END IF;
    END LOOP;

    COMMIT;
END;
/


Mit tud ez a script?
Bejárja az összes PL/SQL forrást.
Felismeri a FROM és JOIN után lévő tábla neveket.
A SELECT utasításból oszlopneveket próbál kinyerni (alap esetben, ha SELECT col1, col2 FROM).
Minden találatot betesz egy struktúrált táblába.

---segédtábla
CREATE TABLE plsql_table_column_usage (
    object_name     VARCHAR2(128),
    object_type     VARCHAR2(30),
    table_alias     VARCHAR2(30),
    table_name      VARCHAR2(128),
    column_name     VARCHAR2(128),
    line_number     NUMBER,
    context         VARCHAR2(30)
);

----script
DECLARE
    CURSOR src_cur IS
        SELECT name, type, line, text
        FROM user_source
        WHERE UPPER(text) LIKE '%SELECT%' OR UPPER(text) LIKE '%FROM%' OR UPPER(text) LIKE '%JOIN%'
           OR UPPER(text) LIKE '%INSERT%' OR UPPER(text) LIKE '%UPDATE%' OR UPPER(text) LIKE '%MERGE%';

    v_line       VARCHAR2(4000);
    v_alias_map  DBMS_SQL.VARCHAR2_TABLE;
    v_object     user_source.name%TYPE;
    v_type       user_source.type%TYPE;
    v_line_num   user_source.line%TYPE;

    PROCEDURE parse_aliases(p_line IN VARCHAR2) IS
        v_from_part VARCHAR2(4000);
        alias_str   VARCHAR2(100);
        tab_str     VARCHAR2(100);
        i           PLS_INTEGER := 1;
    BEGIN
        -- FROM customers c JOIN orders o
        IF REGEXP_LIKE(p_line, '\b(FROM|JOIN)\b\s+[A-Z0-9_]+\s+[A-Z0-9_]+', 'i') THEN
            FOR alias_rec IN (
                SELECT REGEXP_SUBSTR(p_line, '([A-Z0-9_]+)\s+([A-Z0-9_]+)', 1, LEVEL, NULL, 1) AS table_name,
                       REGEXP_SUBSTR(p_line, '([A-Z0-9_]+)\s+([A-Z0-9_]+)', 1, LEVEL, NULL, 2) AS alias
                FROM dual
                CONNECT BY REGEXP_SUBSTR(p_line, '([A-Z0-9_]+)\s+([A-Z0-9_]+)', 1, LEVEL) IS NOT NULL
            ) LOOP
                v_alias_map(alias_rec.alias) := alias_rec.table_name;
            END LOOP;
        END IF;
    END;

    PROCEDURE find_column_references(p_line IN VARCHAR2) IS
        v_token VARCHAR2(100);
        i       PLS_INTEGER := 1;
        alias   VARCHAR2(30);
        column  VARCHAR2(128);
    BEGIN
        -- pl. c.customer_id
        LOOP
            v_token := REGEXP_SUBSTR(p_line, '\b([A-Z0-9_]+)\.([A-Z0-9_]+)\b', 1, i);
            EXIT WHEN v_token IS NULL;
            alias := REGEXP_SUBSTR(v_token, '([A-Z0-9_]+)', 1, 1);
            column := REGEXP_SUBSTR(v_token, '\.([A-Z0-9_]+)', 1, 1);
            column := SUBSTR(column, 2); -- levágjuk a pontot

            IF v_alias_map.EXISTS(alias) THEN
                INSERT INTO plsql_table_column_usage
                VALUES (
                    v_object,
                    v_type,
                    alias,
                    v_alias_map(alias),
                    column,
                    v_line_num,
                    'SELECT/CONTEXT'
                );
            END IF;
            i := i + 1;
        END LOOP;
    END;

BEGIN
    FOR r IN src_cur LOOP
        v_line := UPPER(r.text);
        v_object := r.name;
        v_type := r.type;
        v_line_num := r.line;

        -- Első kör: aliasokat építjük fel
        IF REGEXP_LIKE(v_line, '\b(FROM|JOIN)\b\s+[A-Z0-9_]+\s+[A-Z0-9_]+') THEN
            parse_aliases(v_line);
        END IF;

        -- Második kör: mező hivatkozásokat keresünk
        IF REGEXP_LIKE(v_line, '\b[A-Z0-9_]+\.[A-Z0-9_]+\b') THEN
            find_column_references(v_line);
        END IF;
    END LOOP;

    COMMIT;
END;
/

------  WITH-struktúrák (CTE – Common Table Expression) elemzése összetett feladat

--PL/SQL feldolgozó vázlat (csak 1 szintű CTE-re)
DECLARE
    CURSOR src IS
        SELECT name, type, LISTAGG(text, ' ') WITHIN GROUP (ORDER BY line) AS full_src
        FROM user_source
        WHERE UPPER(text) LIKE '%WITH%'
        GROUP BY name, type;

    v_with_block CLOB;
    v_cte_name VARCHAR2(128);
    v_inner_sql CLOB;
    v_cte_level NUMBER := 0;

BEGIN
    FOR r IN src LOOP
        v_with_block := UPPER(r.full_src);
        v_cte_level := 0;

        -- Egyszerű regex-szel a WITH blokkok kihúzása
        FOR cte_match IN (
            SELECT REGEXP_SUBSTR(v_with_block, 'WITH\s+([A-Z0-9_]+)\s+AS\s*\((.*?)\)', 1, LEVEL, 'i', 0) AS cte_block
            FROM dual
            CONNECT BY REGEXP_SUBSTR(v_with_block, 'WITH\s+([A-Z0-9_]+)\s+AS\s*\((.*?)\)', 1, LEVEL) IS NOT NULL
        ) LOOP
            -- Kinyerjük a CTE nevét
            v_cte_name := REGEXP_SUBSTR(cte_match.cte_block, 'WITH\s+([A-Z0-9_]+)', 1, 1, 'i', 1);
            -- És az al-lekérdezést
            v_inner_sql := REGEXP_SUBSTR(cte_match.cte_block, '\((.*)\)', 1, 1, 'i', 1);

            -- Itt jöhet a belső alias- és oszlop-elemzés (mint korábban)

            -- Beírjuk táblába, hogy a cte_name milyen aliasokat és mezőket tartalmaz
            INSERT INTO plsql_cte_analysis (
                object_name, cte_name, source_table, table_alias, column_name, resolved_from, level_in_cte
            ) VALUES (
                r.name, v_cte_name, 'ORDERS', 'O', 'ORDER_ID', 'ORDERS.ORDER_ID', v_cte_level
            );
            -- stb.

            v_cte_level := v_cte_level + 1;
        END LOOP;
    END LOOP;
    COMMIT;
END;
/


Alternatíva: külső nyelv


A WITH-blokk rekurzív feldolgozására Python + SQL parser sokkal hatékonyabb, pl.:

sqlparse vagy 
sqlglot → ez AST-t is generál!
vagy antlr4

Ezekkel pontos, gráf-alapú visszakövetést is tudsz építeni – és beolvashatod vissza Oracle-be.

(AST = Abstract Syntax Tree, vagyis absztrakt szintaxisfa. Ez a programkód (pl. SQL lekérdezés) struktúráját írja le fa formában, nem csak szövegként.)










SQLServer db




Ez a lekérdezés sqlserver adatbázis esetén kilistázza azokat az objektumokat, amelyek forráskódjában mindkét megadott string (objektumnév) szerepel, függetlenül attól, hogy melyik sorban.

SELECT
    o.name AS object_name,
    o.type_desc AS object_type
FROM
    sys.sql_modules m
INNER JOIN
    sys.objects o ON m.object_id = o.object_id
WHERE
    m.definition LIKE '%OBJECT_NAME_1%'
    AND m.definition LIKE '%OBJECT_NAME_2%'
    -- Opcionális: Szűrés specifikus objektumtípusokra, ha szükséges
    AND o.type IN ('P', 'FN', 'TR', 'V') -- P = Stored Procedure, FN = Scalar function, TR = Trigger, V = View
ORDER BY
    o.name;





Címkék:

Megjegyzések

Megjegyzés küldése