Skip to content
No results
We use cookies to ensure that we give you the best experience on our website.

# Table and column relations in SQL script files

This tool reads a bunch of SQL script files (such as Oracle SQL*Plus command files) that contain SELECT statements, and for each command file read, output the table names and table.column names that the query references in a flat CSV file in which each record in the output file has the same 6-field format like this:

File_Name, Schema_Name, Table_Name, Column_Name, Column_Line_Number, Column_Position

This tool runs like this:

tableColumnRef /path_to_sql/*.sql /o result.txt

let’s take this sql for example:

    SELECT BA.ba_number                PARTNERID,
           BA.ba_name                  "NAME",
           BA.primary_phone_number     PRIMARY_PHONE,
           W.well_number               WELLNUMBER,
           W.well_name,
           CASE W.surface_state_abbr
             WHEN 'TX' THEN W.surface_lgl_tx_block_desc
                            || w.surface_lgl_tx_survey_desc
             WHEN 'OK' THEN W.surface_lgl_section_desc
                            || '-'
                            || W.surface_lgl_township_desc
                            || '-'
                            || W.surface_lgl_range_desc
             ELSE ''
           END                         LEGAL_LOC,
           I.well_integration_id,
           Max(I.effective_begin_date) STARTDATE,
           I.deck_type_code            DECK
    FROM   r_master.interest I
           inner join r_master.interest_detail IDTL
                   ON I.interest_header_id = IDTL.interest_header_id
           inner join r_master.property P
                   ON P.property_id = I.property_id
           inner join r_master.current_well W
                   ON w.well_number = P.property_number
           --W.CURRENT_WELL_INTEGRATION_ID = I.WELL_INTEGRATION_ID
           inner join r_master.business_associate BA
                   ON BA.ba_id = IDTL.owner_ba_id
    WHERE  I.deck_type_code IN DECK
           AND W.well_number LIKE Nvl(:wellid
                                      ||'%', W.well_number)
           AND W.well_name LIKE Nvl(Upper(:wellname)
                                    ||'%', W.well_name)
           AND w.well_number NOT LIKE '19%'
           AND Nvl(W.surface_lgl_section_desc, '1') =
               Nvl(Nvl(:SEC, W.surface_lgl_section_desc), '1')
           AND Nvl(W.surface_lgl_township_desc, '1') = Nvl(
               Nvl(:TWP, W.surface_lgl_township_desc), '1')
           AND Nvl(W.surface_lgl_range_desc, '1') =
               Nvl(Nvl(:RNG, W.surface_lgl_range_desc), '1')
           AND W.surface_state_abbr = Nvl(:state, W.surface_state_abbr)
           AND w.surface_county_name LIKE Nvl(:county
                                              ||'%', w.surface_county_name)
           AND I.is_active_ind = 'Y'
           AND Length(w.well_number) = 6
    GROUP  BY BA.ba_number,
              BA.ba_name,
              BA.primary_phone_number,
              W.well_number,
              W.well_name,
              CASE W.surface_state_abbr
                WHEN 'TX' THEN W.surface_lgl_tx_block_desc
                               || w.surface_lgl_tx_survey_desc
                WHEN 'OK' THEN W.surface_lgl_section_desc
                               || '-'
                               || W.surface_lgl_township_desc
                               || '-'
                               || W.surface_lgl_range_desc
                ELSE ''
              END,
              I.well_integration_id,
              I.deck_type_code
    ORDER  BY BA.ba_name;

result generated by this tool:

    QUERY_NM,SCHEMA_NM,TABLE_NM,COLUMN_NM,LINE_NR,POS_NR
    U_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,BA_NUMBER,2,4
    U_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,BA_NAME,2,27
    U_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,PRIMARY_PHONE_NUMBER,2,45
    U_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,BA_ID,14,49
    U_090203.SQL,R_MASTER,CURRENT_WELL,WELL_NUMBER,3,3
    U_090203.SQL,R_MASTER,CURRENT_WELL,WELL_NAME,3,28
    U_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_STATE_ABBR,4,8
    U_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_TX_BLOCK_DESC,4,44
    U_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_TX_SURVEY_DESC,4,75
    U_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_SECTION_DESC,5,26
    U_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_TOWNSHIP_DESC,5,63
    U_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_RANGE_DESC,5,101
    U_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_COUNTY_NAME,24,7
    U_090203.SQL,R_MASTER,INTEREST,WELL_INTEGRATION_ID,7,3
    U_090203.SQL,R_MASTER,INTEREST,EFFECTIVE_BEGIN_DATE,8,7
    U_090203.SQL,R_MASTER,INTEREST,DECK_TYPE_CODE,9,3
    U_090203.SQL,R_MASTER,INTEREST,INTEREST_HEADER_ID,11,36
    U_090203.SQL,R_MASTER,INTEREST,PROPERTY_ID,12,42
    U_090203.SQL,R_MASTER,INTEREST,IS_ACTIVE_IND,25,7
    U_090203.SQL,R_MASTER,INTEREST_DETAIL,INTEREST_HEADER_ID,11,62
    U_090203.SQL,R_MASTER,INTEREST_DETAIL,OWNER_BA_ID,14,62
    U_090203.SQL,R_MASTER,PROPERTY,PROPERTY_ID,12,26
    U_090203.SQL,R_MASTER,PROPERTY,PROPERTY_NUMBER,13,44

Download this tool (updated, 08-10-2012)