{"id":268,"date":"2010-07-20T02:11:30","date_gmt":"2010-07-20T07:11:30","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=268"},"modified":"2015-05-07T03:44:53","modified_gmt":"2015-05-07T08:44:53","slug":"get-columns-and-tables-in-sql-script","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/","title":{"rendered":"Get columns and tables in SQL script (Java version)"},"content":{"rendered":"

THIS DEMO IS DEPRECIATED SINCE GENERAL SQL PARSER Java VERSION 1.6.0.1 <\/strong>please check this new version<\/a> instead.<\/p>\n

The logic to find all tables and columns in SQL script is quite simple like this:<\/p>\n

1. Parse SQL script.<\/p>\n

2. Iterate all statements via TGSqlParser.sqlstatements and TCustomSqlStatement.getStatements().<\/p>\n

3. Once a statement was found, get all tables belong to this statement via TCustomSqlStatement.tables.<\/p>\n

4. Once a table was found, get all columns belong to this table via TTable.getObjectNameReferences().<\/p>\n

Input SQL:<\/p>\n

    \r\nSELECT e.last_name,\r\n       e.department_id,\r\n       d.department_name\r\nFROM   employees e\r\n       LEFT OUTER JOIN department d\r\n         ON ( e.department_id = d.department_id ); \r\n<\/pre>\nTables and columns:<\/p>\n\r\nTables:\r\ndepartment\r\nemployees\r\n\r\nColumns\r\ndepartment.department_id\r\ndepartment.department_name\r\nemployees.department_id\r\nemployees.last_name\r\n<\/pre>\n.NET version of this document.<\/a><\/p>\nFurthermore, General SQL Parser can detect relationship between column and table even there are derived table in from clause like this:<\/p>\n\r\nselect\r\n       s2.s2t1a1,\r\n       s3.s3t1a1\r\nfrom\r\n    (\r\n      select *\r\n      from subselect2table1 s2t1\r\n    ) s2,\r\n    ( \r\n       select *\r\n       from  subselect3table1, subselect3table2\r\n    ) s3\r\n<\/pre>\nTable and columns detected:<\/p>\n\r\nTables:\r\nsubselect2table1\r\nsubselect3table1\r\nsubselect3table2\r\n\r\nColumns:\r\nsubselect2table1.s2t1a1(table determined:true)\r\nsubselect3table1.s3t1a1(table determined:false)\r\nsubselect3table2.s3t1a1(table determined:false)\r\n<\/pre>\ntable determined:false means that without any further meta information from database, General SQL Parser can’t determine which table s3t1a1 belongs to(subselect3table1 or subselect3table2), but, at least s3t1a1 belongs to one of those 2 tables.<\/p>\nSince Java version 1.4.1.4, a new public interface IMetaDatabase was added, so user can implements IMetaDatabase to provide detailed meta information from database to help SQL parser to determine relationship between column and tables. With the help of those meta information, problems mentioned above can be solved automatically by General SQL Parser.<\/p>\nFor a more complicated Oracle plsql stored procedure:<\/p>\n\r\nCREATE OR REPLACE PACKAGE BODY G_RECALC_PKG\r\nIS\r\n  FUNCTION RESERVE_STATE(LBYORA IN INTEGER)\r\n  RETURN INTEGER\r\n  IS\r\n    l_count   NUMBER;\r\n    r_inv     G_RECALC_INVALIDATE_STATE % ROWTYPE;\r\n    l_timeout NUMBER;\r\n  BEGIN\r\n      l_timeout := 1200;\r\n\r\n      l_count := 0;\r\n\r\n      -- Checking consistency in the database : excactly one row should exist there\r\n      SELECT COUNT(*)\r\n      INTO   l_count\r\n      FROM   G_RECALC_INVALIDATE_STATE;\r\n\r\n      IF l_count > 1 THEN\r\n        SELECT MAX(LTIMEOUTSECS)\r\n        INTO   l_timeout\r\n        FROM   G_RECALC_INVALIDATE_STATE;\r\n\r\n        DELETE FROM G_RECALC_INVALIDATE_STATE;\r\n\r\n        l_count := 0;\r\n      END IF;\r\n\r\n      IF l_count = 0 THEN\r\n        CLEAN_PREVIOUS_VERSION;\r\n\r\n        INSERT INTO G_RECALC_INVALIDATE_STATE\r\n                    (TLASTUPDATED,\r\n                     LTIMEOUTSECS,\r\n                     TNEXTFREEORATIME,\r\n                     LRUNCOUNT,\r\n                     LMAXELAPSEDSECS,\r\n                     LLATESTRUN)\r\n        VALUES      (NULL,\r\n                     l_timeout,\r\n                     NULL,\r\n                     0,\r\n                     0,\r\n                     0);\r\n      END IF;\r\n\r\n      SELECT TLASTUPDATED,\r\n             LTIMEOUTSECS,\r\n             TNEXTFREEORATIME\r\n      INTO   R_INV.TLASTUPDATED, R_INV.LTIMEOUTSECS, R_INV.TNEXTFREEORATIME\r\n      FROM   G_RECALC_INVALIDATE_STATE\r\n      FOR UPDATE WAIT 10;\r\n\r\n      COMMIT;\r\n\r\n      RETURN 1;\r\n  EXCEPTION\r\n    WHEN OTHERS THEN\r\n               COMMIT;\r\n\r\n               RETURN 0;\r\n  END RESERVE_STATE;\r\nEND G_RECALC_PKG;\r\n\r\n\/ \r\n<\/pre>\nTable and columns detected:<\/p>\n\r\nTables:\r\ng_recalc_invalidate_state\r\n\r\nColumns:\r\ng_recalc_invalidate_state.*(table determined:true)\r\ng_recalc_invalidate_state.llatestrun(table determined:true)\r\ng_recalc_invalidate_state.lmaxelapsedsecs(table determined:true)\r\ng_recalc_invalidate_state.lruncount(table determined:true)\r\ng_recalc_invalidate_state.ltimeoutsecs(table determined:true)\r\ng_recalc_invalidate_state.tlastupdated(table determined:true)\r\ng_recalc_invalidate_state.tnextfreeoratime(table determined:true)\r\n<\/pre>\nDownload this demo: Java version<\/a>,
    \nC# version demo1<\/a>,
    \nC# version demo2<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"THIS DEMO IS DEPRECIATED SINCE GENERAL SQL PARSER Java VERSION 1.6.0.1 please check this new version instead. The logic to find all tables and columns in SQL script is quite simple like this: 1. Parse SQL script. 2. Iterate all statements via TGSqlParser.sqlstatements and TCustomSqlStatement.getStatements(). 3. Once a statement was found, get all tables belong to this statement via TCustomSqlStatement.tables. 4. Once a table was found, get all columns belong to this table via TTable.getObjectNameReferences(). Input SQL: SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN department d ON ( e.department_id = d.department_id ); Tables and columns: Tables: department\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":235,"menu_order":0,"comment_status":"open","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