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)