Is there a way to parser a SQL query to pull out the column names and table names?

Question

I have 150+ SQL queries in separate text files that I need to analyze (just the actual SQL code, not the data results) in order to identify all column names and table names used. Preferably with the number of times each column and table makes an appearance. Writing a brand new SQL parsing program is trickier than is seems, with nested SELECT statements and the like.

There has to be a program, or code out there that does this (or something close to this), but I have not found it.

Answer 1

You may want to looking to something like this:

JSqlParser

which uses JavaCC to parse and return the query string as an object graph. I've never used it, so I can't vouch for its quality.

Answer 2

How about using the Execution Plan report in MS SQLServer? You can save this to an xml file which can then be parsed.

Answer 3

If you're application needs to do it, and has access to a database that has the tables etc, you could run something like:

SELECT TOP 0 * FROM MY_TABLE

Using ADO.NET. This would give you a DataTable instance for which you could query the columns and their attributes.

Answer 4

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

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