SQL parser library for Java - Retrieve the list of table names present in a SQL statement

Question

I am looking for a SQL Library that will parse an SQL statement and return some sort of Object representation of the SQL statement. My main objective is actually to be able to parse the SQL statement and retrieve the list of table names present in the SQL statement (including subqueries, joins and unions).

I am looking for a free library with a license business friendly (e.g. Apache license). I am looking for a library and not for an SQL Grammar. I do not want to build my own parser.

The best I could find so far was JSQLParser, and the example they give is actually pretty close to what I am looking for. However it fails parsing too many good queries (DB2 Database) and I'm hoping to find a more reliable library.

Solution 1

I doubt you'll find anything prewritten that you can just use. The problem is that ISO/ANSI SQL is a very complicated grammar — something like more than 600 production rules IIRC.

Terence Parr's ANTLR parser generator (Java, but can generate parsers in any one of a number of target languages) has several SQL grammars available, including a couple for PL/SQL, one for a SQL Server SELECT statement, one for mySQL, and one for ISO SQL.

No idea how complete/correct/up-to-date they are.

http://www.antlr.org/grammar/list

Solution 2

You needn't reinvent the wheel, there is already such a reliable SQL parser library there, (it's commerical, not free), and this article shows how to retrieve the list of table names present in the SQL statement (including subqueries, joins and unions) that is exactly what you are looking for.

http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/get-columns-and-tables-in-sql-script/

This SQL parser library supports Oracle, SQL Server, DB2, MySQL, Teradata and ACCESS.

Solution 3

You need the ultra light, ultra fast library to extract table names from SQL (Disclaimer: I am the owner)

Just add the following in your pom

<dependency>
 <groupId>com.github.mnadeem</groupId>
 <artifactId>sql-table-name-parser</artifactId>
 <version>0.0.1</version>

And do the following

new TableNameParser(sql).tables()

For more details, refer the project