How could I infer parameters & returned columns from a SQL query string?

Question

I'm building an application that allows users to input SQL query strings. These query strings will contain 0 or more parameters and 1 or more returned columns. Is there a standard approach to parsing SQL queries to extract these elements? Ideally, this would be without running the query, or even being connected to an instance of SQL Server.

A query string might look like this:

SELECT
    Posts.ID,
    Posts.Description
FROM Posts
WHERE Posts.Date > @StartDate

And from this, I'd like to extract a collections of column names ("Posts.ID", "Posts.Description") and a collection of parameters ("StartDate").

This doesn't seem like a particularly strange thing to do. Indeed, Microsoft do this in their reporting products (I've seen it in BIDS).

Is there a library I can use? Otherwise, what's the recommended approach?

you can add a condition like and '1=2' and then get the result set, then you can iterate through columns collection and find out the types of the columns. I dont think that it is possible to get that without executing the query, even if you can parse out the table and column names you still need some kind of dictionary to find out what type they are. — hazimdikenli, Jan 14 '15 at 13:02

score 0 · Accepted Answer · answered Jan 14 '15 at 14:09

I don't know of any way to get this info without connecting to a SQL Server but for SQL Server 2012 onwards there are some new System stored procedures that might help;

Prior to SQL Server 2012 you could execute a query using SET FMTONLY ON to get the resultset schema but you'd need to know the parameters.

Hope this helps,

Rhys

How could I infer parameters & returned columns from a SQL query string?

1 Answers1