{"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":"<p><strong>THIS DEMO IS DEPRECIATED SINCE GENERAL SQL PARSER Java VERSION 1.6.0.1 <\/strong>please <a href=\"http:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/listing-all-table-and-column-references\/\">check this new version<\/a> instead.<\/p>\n<p>The logic to find all tables and columns in SQL script is quite simple like this:<\/p>\n<p>1. Parse SQL script.<\/p>\n<p>2. Iterate all statements via TGSqlParser.sqlstatements and TCustomSqlStatement.getStatements().<\/p>\n<p>3. Once a statement was found, get all tables belong to this statement via TCustomSqlStatement.tables.<\/p>\n<p>4. Once a table was found, get all columns belong to this table via TTable.getObjectNameReferences().<\/p>\n<p>Input SQL:<\/p>\n<pre>\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>\n<p>Tables and columns:<\/p>\n<pre>\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<p><a href=\"http:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script-net-version\/\">.NET version of this document.<\/a><\/p>\n<p>Furthermore, General SQL Parser can detect relationship between column and table even there are derived table in from clause like this:<\/p>\n<pre>\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>\n<p>Table and columns detected:<\/p>\n<pre>\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>\n<p>table determined:false means that without any further meta information from database, General SQL Parser can&#8217;t determine which table s3t1a1 belongs to(subselect3table1 or subselect3table2), but, at least s3t1a1 belongs to one of those 2 tables.<\/p>\n<p>Since 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>\n<p>For a more complicated Oracle plsql stored procedure:<\/p>\n<pre>\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>\n<p>Table and columns detected:<\/p>\n<pre>\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>\n<p>Download this demo: <a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/java\/gettablecolumns\/gettablecolumns.zip\">Java version<\/a>,<br \/>\n<a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/csharp\/gettablecolumn\/getTableColumn.zip\">C# version demo1<\/a>,<br \/>\n<a href=\"http:\/\/www.dpriver.com\/gsp\/demos\/csharp\/gettable\/getTable.cs\">C# version demo2<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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":"<!-- This site is optimized with the Yoast SEO plugin v19.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Help you to make better use of General SQL Parser<\/title>\n<meta name=\"description\" content=\"Get columns and tables in SQL script (Java version)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Help you to make better use of General SQL Parser\" \/>\n<meta property=\"og:description\" content=\"Get columns and tables in SQL script (Java version)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2015-05-07T08:44:53+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/\",\"name\":\"Help you to make better use of General SQL Parser\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2010-07-20T07:11:30+00:00\",\"dateModified\":\"2015-05-07T08:44:53+00:00\",\"description\":\"Get columns and tables in SQL script (Java version)\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"List of demos illustrate how to use general sql parser\",\"item\":\"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Get columns and tables in SQL script (Java version)\"}]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Help you to make better use of General SQL Parser","description":"Get columns and tables in SQL script (Java version)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/","og_locale":"en_US","og_type":"article","og_title":"Help you to make better use of General SQL Parser","og_description":"Get columns and tables in SQL script (Java version)","og_url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/","og_site_name":"SQL and Data Blog","article_modified_time":"2015-05-07T08:44:53+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/","url":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/","name":"Help you to make better use of General SQL Parser","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2010-07-20T07:11:30+00:00","dateModified":"2015-05-07T08:44:53+00:00","description":"Get columns and tables in SQL script (Java version)","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/get-columns-and-tables-in-sql-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"List of demos illustrate how to use general sql parser","item":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/"},{"@type":"ListItem","position":3,"name":"Get columns and tables in SQL script (Java version)"}]}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/268"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=268"}],"version-history":[{"count":25,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/268\/revisions"}],"predecessor-version":[{"id":1648,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/268\/revisions\/1648"}],"up":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/pages\/235"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}