How can I parse T-SQL in AST using classes in Microsoft.SqlServer.Management.SqlParser

Question

The Parser class only has a Parse method that returns ParseResult , and it seems I can do nothing with ParseResult . How can I get the abstract syntax tree of my sql statement, or just metadata markers can be parsed from sql in order.

Answer 1

I did some research and found that I can use reflection to generate a parsed XML information file using SqlScript.WriteXml . And here is a sample code, I don't know if there is a better way.

var rst = Parser.Parse(File.ReadAllText(@"*.sql")); var fieldInfo = rst.GetType().GetField("sqlScript", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.GetField); var script = fieldInfo.GetValue(rst); var writer = XmlWriter.Create("*.xml"); script.GetType().InvokeMember("WriteXml", BindingFlags.NonPublic| BindingFlags.Instance | BindingFlags.InvokeMethod , null, script, new object[] { writer }); writer.Close(); 

Answer 2

I was excited you found AST in general! Using it directly requires the use of dynamic variables to access the collection of .Child objects in the internal Microsoft.SqlServer.Management.SqlParser.SqlCodeDom namespace.

Instead of calling WriteXml, I recommend referring to an existing Xml string property. This eliminates the need to solve problems caused by nesting SQL comments in XML comments (cannot have -- inside an XML comment, -- becomes - - ).

 var rst = Parser.Parse(File.ReadAllText(@"*.sql")); var script = rst.GetType().GetProperty("Script", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(rst); var xml = script.GetType().BaseType.GetProperty("Xml").GetValue(script) as String; 

If you are genuinely willing to accept only metadata tokens, I found an example of PowerShell here; it is working on something like this (detailed metadata of the factory template):

 // using Microsoft.SqlServer.Management.SqlParser.Parser; // ... var sql = File.ReadAllText(@"*.sql"); var scanner = new Scanner(new ParseOptions()); int scannerState = 0; scanner.SetSource(sql, 0); var allTokens = new List<MSSQL_Token_JS>(); MSSQL_Token_JS curToken = null; do { curToken = MSSQL_Token_JS.GetNext(scanner, sql, ref scannerState); allTokens.Add(curToken); } while (curToken.Value != Tokens.EOF); //... public class MSSQL_Token_JS { public readonly string SourceSQL; public readonly Tokens Value; public readonly string Text; public readonly int ScannerState; public readonly int Start; public readonly int End; public readonly bool IsPairMatch; public readonly bool IsExecAutoParamHelp; private MSSQL_Token_JS(string SourceSQL, int tokenId, int ScannerState, int Start, int End, bool IsPairMatch, bool IsExecAutoParamHelp) { this.SourceSQL = SourceSQL; this.Value = (Tokens)tokenId; if (this.Value != Tokens.EOF) { this.Text = SourceSQL.Substring(Start, End - Start + 1); } this.ScannerState = ScannerState; this.Start = Start; this.End = End; this.IsPairMatch = IsPairMatch; this.IsExecAutoParamHelp = IsExecAutoParamHelp; } public static MSSQL_Token_JS GetNext(Scanner scanner, string SourceSQL, ref int ScannerState) { int start, end; bool isPairMatch, isExecAutoParamHelp; int tokenId = scanner.GetNext(ref ScannerState, out start, out end, out isPairMatch, out isExecAutoParamHelp); return new MSSQL_Token_JS(SourceSQL, tokenId, ScannerState, start, end, isPairMatch, isExecAutoParamHelp); } public override string ToString() { return String.Format("{0}:{1}", this.Value, this.Text); } }