Is there a way to parser a SQL query to pull out the column names and table names?
Solution 1
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.
Solution 2
How about using the Execution Plan report in MS SQLServer? You can save this to an xml file which can then be parsed.
Solution 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.
Solution 4
You may want to looking to something like this:
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.
Jason Butler
Life and liberty lovin' father of two awesome girls. I develop web apps and live by one far reaching principle the initiation of force is always wrong.
Updated on July 29, 2022Comments
-
Jason Butler almost 2 years
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.
-
Boris Milner over 10 yearsSaved my *$$ :) Thank you.