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

Question

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...

Answer 1

You can use Microsoft.Data.Schema.ScriptDom for this. I'm not familiar with it myself but I just tried parsing your statement and could see that the variables were accessible in the ScriptTokenStream collection (not sure if there is an easier way of getting hold of them or not)

Edit: Posting the OP's own code from the comments!

    var sql = "SELECT @Foo [Foo], '@Bar' [Bar], @Baz [Baz]";
    var p = new TSql100Parser(true);
    var errors = new List<ParseError>();
    var tokens = p.GetTokenStream(new StringReader(sql), errors);
    if (errors.Count == 0)
    {
        var variables = (from t in tokens where t.TokenType == 
                       TSqlTokenType.Variable select t.Text).ToArray();
    }

SQL Server 2012 also introduces sp_describe_undeclared_parameters which is of relevance but fails with this example as it needs to be able to deduce datatypes.

Answer 2

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();
        }
    }