How can I determine the parameters required by an arbitrary piece of T-SQL?

Basically, I'm looking for an equivalent to SqlCommandBuilder.DeriveParameters that will work for arbitrary T-SQL.

For example, this query requires one parameter:

SELECT @Foo [Foo], '@Bar' [Bar], @Baz [Baz]

I basically need to extract:

new[] { "Foo", "Baz" }

From above. I could build a SQL parser, but I have an open connection to SQL server, so I'd prefer to use an existing option if possible.

Edit:

There has to be a way to do this, because SQL Server's Business Intelligence Development Studio is able to do this very successfully.

Edit 2:

SQL BIDS is executing this command in order to describe the results:

exec sp_executesql N'SET FMTONLY OFF;SET FMTONLY ON;SELECT @Foo [Foo], ''@Bar'' [Bar], @Baz [Baz]',
    N'@Foo sql_variant,@Baz sql_variant',
    @Foo=NULL,@Baz=NULL

Which explains how it can determine the columns, but it may be just string parsing to get the parameters...

c# .net t-sql reflection

Edit Report

Use RegEx and parse the parameters, I quickly tested this, so not sure if will work, might need modiciation.

[^']@([^[,]+)

If you need to modify the regular expression string, I find this site very helpful: http://regexlib.com/RETester.aspx

public Form1()
    {
        InitializeComponent();

        string query = "SELECT @Foo [Foo], '@Bar' [Bar], @Baz [Baz] ";
        Regex r = new Regex(@"[^']@([^\[,]+)");
        MatchCollection collection = r.Matches(query);
        string[] array = new string[collection.Count];

        for (int i = 0; i < collection.Count; i++)
        {
            array[i] = collection[i].Value.Trim();

            // If you want the string[] populated without the "@"
            // array[i] = collection[i].Groups[1].Value.Trim();
        }
    }

answered 2011-04-26T15:35:15.803

How can I determine the parameters required by an arbitrary piece of T-SQL?

1 Answer

Your Answer