Parse/deconstruct SQL with VBA-Access

Question

Does anyone know of a way to deconstruct a SQL statement (take a select SQL statement, extract columns from each SELECT, tables from each FOR and each JOIN, and filtering criteria from each WHERE. I can then put this data into a BOM table to create a "map" of the query), including subqueries, using VBA? I have a project to map Teradata views into a Access DB. I'd like to have an automated method to do this.

Answer 1

You want access to arbitrary substructures of a SQL query (incuding sub SELECTs)? What you need is a full parser for the SQL dialect of interest.

SQL is a pretty large and complicated language. It is possible to hand-code a recursive descent parser to do this, but that's quite a lot of work. You'd be likely better off with a parser generator and an SQL BNF to feed it.

But the fact that you want to do this in VBA hints that you are unlikely to find such a parser generator. You may have to call a parser generator coded in another langauge (e.g., C#) if you want to have a reasonable chance of doing this with modest effort, and go find a preexisting SQL parser.

Answer 2

Check this article, It shows how to generate internal query parse tree of various sql statements including select statement with subquery in xml for further processing.

It using a C# sql parser.