{"id":1406,"date":"2012-06-14T09:34:03","date_gmt":"2012-06-14T14:34:03","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1406"},"modified":"2012-08-10T01:49:52","modified_gmt":"2012-08-10T06:49:52","slug":"table-and-column-relations-in-sql-script-files","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/table-and-column-relations-in-sql-script-files\/","title":{"rendered":"Table and column relations in SQL script files"},"content":{"rendered":"

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:<\/p>\n

File_Name, Schema_Name, Table_Name, Column_Name, Column_Line_Number, Column_Position<\/p>\n

This tool runs like this:<\/p>\n

tableColumnRef \/path_to_sql\/*.sql \/o result.txt<\/p>\n

let’s take this sql for example:<\/p>\n

    \r\nSELECT BA.ba_number                PARTNERID, \r\n       BA.ba_name                  \"NAME\", \r\n       BA.primary_phone_number     PRIMARY_PHONE, \r\n       W.well_number               WELLNUMBER, \r\n       W.well_name, \r\n       CASE W.surface_state_abbr \r\n         WHEN 'TX' THEN W.surface_lgl_tx_block_desc \r\n                        || w.surface_lgl_tx_survey_desc \r\n         WHEN 'OK' THEN W.surface_lgl_section_desc \r\n                        || '-' \r\n                        || W.surface_lgl_township_desc \r\n                        || '-' \r\n                        || W.surface_lgl_range_desc \r\n         ELSE '' \r\n       END                         LEGAL_LOC, \r\n       I.well_integration_id, \r\n       Max(I.effective_begin_date) STARTDATE, \r\n       I.deck_type_code            DECK \r\nFROM   r_master.interest I \r\n       inner join r_master.interest_detail IDTL \r\n               ON I.interest_header_id = IDTL.interest_header_id \r\n       inner join r_master.property P \r\n               ON P.property_id = I.property_id \r\n       inner join r_master.current_well W \r\n               ON w.well_number = P.property_number \r\n       --W.CURRENT_WELL_INTEGRATION_ID = I.WELL_INTEGRATION_ID \r\n       inner join r_master.business_associate BA \r\n               ON BA.ba_id = IDTL.owner_ba_id \r\nWHERE  I.deck_type_code IN DECK \r\n       AND W.well_number LIKE Nvl(:wellid \r\n                                  ||'%', W.well_number) \r\n       AND W.well_name LIKE Nvl(Upper(:wellname) \r\n                                ||'%', W.well_name) \r\n       AND w.well_number NOT LIKE '19%' \r\n       AND Nvl(W.surface_lgl_section_desc, '1') = \r\n           Nvl(Nvl(:SEC, W.surface_lgl_section_desc), '1') \r\n       AND Nvl(W.surface_lgl_township_desc, '1') = Nvl( \r\n           Nvl(:TWP, W.surface_lgl_township_desc), '1') \r\n       AND Nvl(W.surface_lgl_range_desc, '1') = \r\n           Nvl(Nvl(:RNG, W.surface_lgl_range_desc), '1') \r\n       AND W.surface_state_abbr = Nvl(:state, W.surface_state_abbr) \r\n       AND w.surface_county_name LIKE Nvl(:county \r\n                                          ||'%', w.surface_county_name) \r\n       AND I.is_active_ind = 'Y' \r\n       AND Length(w.well_number) = 6 \r\nGROUP  BY BA.ba_number, \r\n          BA.ba_name, \r\n          BA.primary_phone_number, \r\n          W.well_number, \r\n          W.well_name, \r\n          CASE W.surface_state_abbr \r\n            WHEN 'TX' THEN W.surface_lgl_tx_block_desc \r\n                           || w.surface_lgl_tx_survey_desc \r\n            WHEN 'OK' THEN W.surface_lgl_section_desc \r\n                           || '-' \r\n                           || W.surface_lgl_township_desc \r\n                           || '-' \r\n                           || W.surface_lgl_range_desc \r\n            ELSE '' \r\n          END, \r\n          I.well_integration_id, \r\n          I.deck_type_code \r\nORDER  BY BA.ba_name; \r\n<\/pre>\nresult generated by this tool:<\/p>\n\r\nQUERY_NM,SCHEMA_NM,TABLE_NM,COLUMN_NM,LINE_NR,POS_NR\r\nU_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,BA_NUMBER,2,4\r\nU_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,BA_NAME,2,27\r\nU_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,PRIMARY_PHONE_NUMBER,2,45\r\nU_090203.SQL,R_MASTER,BUSINESS_ASSOCIATE,BA_ID,14,49\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,WELL_NUMBER,3,3\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,WELL_NAME,3,28\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_STATE_ABBR,4,8\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_TX_BLOCK_DESC,4,44\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_TX_SURVEY_DESC,4,75\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_SECTION_DESC,5,26\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_TOWNSHIP_DESC,5,63\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_LGL_RANGE_DESC,5,101\r\nU_090203.SQL,R_MASTER,CURRENT_WELL,SURFACE_COUNTY_NAME,24,7\r\nU_090203.SQL,R_MASTER,INTEREST,WELL_INTEGRATION_ID,7,3\r\nU_090203.SQL,R_MASTER,INTEREST,EFFECTIVE_BEGIN_DATE,8,7\r\nU_090203.SQL,R_MASTER,INTEREST,DECK_TYPE_CODE,9,3\r\nU_090203.SQL,R_MASTER,INTEREST,INTEREST_HEADER_ID,11,36\r\nU_090203.SQL,R_MASTER,INTEREST,PROPERTY_ID,12,42\r\nU_090203.SQL,R_MASTER,INTEREST,IS_ACTIVE_IND,25,7\r\nU_090203.SQL,R_MASTER,INTEREST_DETAIL,INTEREST_HEADER_ID,11,62\r\nU_090203.SQL,R_MASTER,INTEREST_DETAIL,OWNER_BA_ID,14,62\r\nU_090203.SQL,R_MASTER,PROPERTY,PROPERTY_ID,12,26\r\nU_090203.SQL,R_MASTER,PROPERTY,PROPERTY_NUMBER,13,44\r\n<\/pre>\nDownload this tool<\/a> (updated, 08-10-2012)<\/p>\n","protected":false},"excerpt":{"rendered":"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 ||\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n