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?

Answer 1

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;

• sp_describe_undeclared_parameters
• sp_describe_first_result_set

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