Is there a way to parse a SQL query to infer column names and table names?

Question

I have 150+ SQL queries in separate text files that I need to parse (only the actual SQL code, not the data results) to identify all column names and table names used. Preferably the number of times a column and table appears. Writing a new SQL parser is harder than it sounds with nested SELECT statements, etc.

There should be a program or code that does this (or something close to this), but I did not find it.

Answer 1

I actually used a tool called SQL Pretty Printer . You can purchase the desktop version, but I just used the free online application. Just copy the query into the text box, set Output to "List DB Object" and click the Format SQL button.

It works great using around 150 different (and complex) SQL queries.

Answer 2

How to use the execution plan report in MS SQLServer? You can save this to an XML file, which can then be parsed.

Answer 3

You might want to look at something like this:

Jsqlparser

which uses JavaCC to parse and return a query string as a graph of objects. I never used it, so I can not vouch for its quality.

Answer 4

If you need an application, and you have access to a database with tables, etc., you can run something like:

SELECT TOP 0 * FROM MY_TABLE 

Using ADO.NET. This will give you an instance of a DataTable for which you can query the columns and their attributes.

Answer 5

Please go with antlr ... Write the grammar n following the steps .. that are given on the antlr website. First you get an AST (abstract syntax tree). For this query ... we can go through this and bring the whole table, the column present in the query.