Parse SQL statement

Question

How can I parse a SQL statement (for SQL Server) to extract columns and parameters info (Name, DataType) without using ActiveQueryBuilder.

Regards

If you have access to the database and the name of the procedure/view/whatever, you could just look in the `information_schema` tables to find parameters/columns and so on without parsing a statement. — Bridge, Sep 25 '12 at 10:14
Which version of SQL Server? For SQL Server 2012, you might use the Microsoft SQL Server 2012 Transact-SQL Language Service. — David Brabant, Sep 25 '12 at 10:15
possible duplicate of [Parsing SQL code in C#](http://stackoverflow.com/questions/589096/parsing-sql-code-in-c-sharp) — Justin, Sep 25 '12 at 10:30

Aghilas Yakoub · Answer 1 · 2012-09-25T10:09:31.477

You can try with TSql100Parser class

Link : http://msdn.microsoft.com/fr-fr/library/microsoft.data.schema.scriptdom.sql.tsql100parser.aspx

Sample :

 bool fQuotedIdenfifiers = false;
 var _parser = new TSql100Parser(fQuotedIdenfifiers);

 SqlScriptGeneratorOptions options = new SqlScriptGeneratorOptions();
 options.SqlVersion = SqlVersion.Sql100;
 options.KeywordCasing = KeywordCasing.UpperCase;
 _scriptGen = new Sql100ScriptGenerator(options);


 IScriptFragment fragment;
 IList<ParseError> errors;
 using (StringReader sr = new StringReader(inputScript))
 {
       fragment = _parser.Parse(sr, out errors);
 }

 if (errors != null && errors.Count > 0)
 {
       StringBuilder sb = new StringBuilder();
       foreach (var error in errors)
       {
           sb.AppendLine(error.Message);
           sb.AppendLine("offset " + error.Offset.ToString());
       }
       var errorsList = sb.ToString();
 }
 else
 {
       String script;
       _scriptGen.GenerateScript(fragment, out script);
       var result = script;
 }

Thanks guys. I'm struggling with sql after being a desktop applications programmer for 25+ years. My current position requires that I take my sql experience to a whole new level. understanding just how differently sql is parsed as compared to other procedural languages is a hugh plus. Mega DITTOS! — garaber, Jan 12 '15 at 18:58

score 1 · Answer 2 · answered Sep 25 '12 at 10:11

Well, parsing the statement is much different than getting back the schema of the resulting statement. Parsing would mean your just validating the syntax of the query. However, to get back the resulting schema involves parsing so how about you try this.

DataTable table = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter("select * from yourtable", "your connection string");
sda.FillSchema(table, SchemaType.Source);

Parse SQL statement

2 Answers2

Linked