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

How are you building up this initial string? Is there something you can use that you're using to dynamically build up this string to find the required parameters? — mservidio, Apr 26 '11 at 15:35
Does SQL Profiler show anything interesting if you watch the server while getting Business Intelligence Development Studio to do this? Would tell us if it's a server function or just string parsing — AakashM, Apr 26 '11 at 15:36
Perhaps just use a regular expression to find the @variables that aren't enclosed within quotes... that should work I think. — mservidio, Apr 26 '11 at 15:38
Are you aware of [Microsoft.Data.Schema.ScriptDom](http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx)? — Martin Smith, Apr 26 '11 at 15:47
@Martin; OMG! I think that is what I need. Please post that as an answer. — John Gietzen, Apr 26 '11 at 15:51
@Martin: Post this code: ` var sql = "SELECT @Foo [Foo], '@Bar' [Bar], @Baz [Baz]"; var p = new TSql100Parser(true); var errors = new List(); 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(); }` — John Gietzen, Apr 26 '11 at 16:04
@John - Thanks. That's much more concise than what I was trying! — Martin Smith, Apr 26 '11 at 16:11

Martin Smith · Accepted Answer · 2015-05-31T22:05:13.673

12

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.

edited May 31 '15 at 22:05

answered Apr 26 '11 at 16:04

Martin Smith

438,706
87
741
845

How can I use this to get the tables in a SQL query. I tried replacing Variable with Table in your code. It's not working. When I use Identifier it gives both column names and table names. Is there a way to get only table names? – Deepan Cool Jan 23 '17 at 19:59
@DeepanCool Gert Draper's answer here looks like it will do that https://social.msdn.microsoft.com/Forums/sqlserver/en-US/24fd8fa5-b1af-44e0-89a2-a278bbf11ae0/parsing?forum=ssdt – Martin Smith Jan 23 '17 at 20:34

mservidio · Answer 2 · 2011-04-26T15:48:21.817

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

score 0 · Answer 3 · edited May 23 '17 at 11:51

0

I think SqlCommandBuilder.DeriveParameters works by asking SQL Server what the parameters are, which is not an option in this case. Some sort of string processing like a RegEx is probably your best bet.

This Regex might be able to do it

@([_a-zA-Z]+)

Haven't tried it myself, but its from this similar question

It will also find parameters in comments and quoted strings but its a start.

edited May 23 '17 at 11:51

Community

1
1

answered Apr 26 '11 at 15:43

codeulike

22,514
29
120
167

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

3 Answers3

Linked