Get columns and tables in SQL script (Java version)

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
employees

Columns
department.department_id
department.department_name
employees.department_id
employees.last_name

.NET version of this document.

Furthermore, General SQL Parser can detect relationship between column and table even there are derived table in from clause like this:

select
       s2.s2t1a1,
       s3.s3t1a1
from
    (
      select *
      from subselect2table1 s2t1
    ) s2,
    ( 
       select *
       from  subselect3table1, subselect3table2
    ) s3

Table and columns detected:

Tables:
subselect2table1
subselect3table1
subselect3table2

Columns:
subselect2table1.s2t1a1(table determined:true)
subselect3table1.s3t1a1(table determined:false)
subselect3table2.s3t1a1(table determined:false)

table 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.

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.

For a more complicated Oracle plsql stored procedure:

CREATE OR REPLACE PACKAGE BODY G_RECALC_PKG
IS
  FUNCTION RESERVE_STATE(LBYORA IN INTEGER)
  RETURN INTEGER
  IS
    l_count   NUMBER;
    r_inv     G_RECALC_INVALIDATE_STATE % ROWTYPE;
    l_timeout NUMBER;
  BEGIN
      l_timeout := 1200;

      l_count := 0;

      -- Checking consistency in the database : excactly one row should exist there
      SELECT COUNT(*)
      INTO   l_count
      FROM   G_RECALC_INVALIDATE_STATE;

      IF l_count > 1 THEN
        SELECT MAX(LTIMEOUTSECS)
        INTO   l_timeout
        FROM   G_RECALC_INVALIDATE_STATE;

        DELETE FROM G_RECALC_INVALIDATE_STATE;

        l_count := 0;
      END IF;

      IF l_count = 0 THEN
        CLEAN_PREVIOUS_VERSION;

        INSERT INTO G_RECALC_INVALIDATE_STATE
                    (TLASTUPDATED,
                     LTIMEOUTSECS,
                     TNEXTFREEORATIME,
                     LRUNCOUNT,
                     LMAXELAPSEDSECS,
                     LLATESTRUN)
        VALUES      (NULL,
                     l_timeout,
                     NULL,
                     0,
                     0,
                     0);
      END IF;

      SELECT TLASTUPDATED,
             LTIMEOUTSECS,
             TNEXTFREEORATIME
      INTO   R_INV.TLASTUPDATED, R_INV.LTIMEOUTSECS, R_INV.TNEXTFREEORATIME
      FROM   G_RECALC_INVALIDATE_STATE
      FOR UPDATE WAIT 10;

      COMMIT;

      RETURN 1;
  EXCEPTION
    WHEN OTHERS THEN
               COMMIT;

               RETURN 0;
  END RESERVE_STATE;
END G_RECALC_PKG;

/ 

Table and columns detected:

Tables:
g_recalc_invalidate_state

Columns:
g_recalc_invalidate_state.*(table determined:true)
g_recalc_invalidate_state.llatestrun(table determined:true)
g_recalc_invalidate_state.lmaxelapsedsecs(table determined:true)
g_recalc_invalidate_state.lruncount(table determined:true)
g_recalc_invalidate_state.ltimeoutsecs(table determined:true)
g_recalc_invalidate_state.tlastupdated(table determined:true)
g_recalc_invalidate_state.tnextfreeoratime(table determined:true)

Download this demo: Java version,
C# version demo1,
C# version demo2