A question about Microsoft.SqlServer.SqlParser.dll

Question

Hi all,

I found this library in SSMS 2008 assemblies, it can parse T-SQL statements into structured XML as well. 
'Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(SQL)'
This function can resolve standard select/update/insert statements, and it's very useful to us for advance code policies check,  but it's not for all sql commands, for example: 'CREATE TABLE' is okay to go, but 'ALTER TABLE' is not. why?
And one thing more, after SQL Server 2008 R2 is installed, this assembly disappeared.:(

Is it a half-done job?
Where can I find the final version?

Answer 1

Instead of Microsoft.SqlServer.SqlParser.dll you should use this: http://msdn.microsoft.com/en-us/library/dd193281.aspx

This is part of the visual studio 2008/2010. You need reference following dlls: Microsoft.Data.Schema.ScriptDom

Microsoft.Data.Schema.ScriptDom.Sql

example: var parser = new TSql100Parser(exportOptions.InitialQuotedIdentifier);

using (var sr = new StringReader(Sqldefinition))

{ IList<ParseError> errors;

IScriptFragment fregment = this.parser.Parse(sr, out errors);

if (errors != null && errors.Count > 0)

{ return false; }

}

---

István Sáfár

Answer 2

Hi István Sáfár,

Thank you for your response.

I can get your idea, and the Microsoft.Data.Schema.ScriptDom is cool.

After some tests, I found it just returns the collection of tokins, that means we will have to re-design hundreds of formulas which were made by XML definition, this could be a big trouble to us.

What I need is the structured xml data source. anyway, XML policy definition is much more easier for DBA guys.

Sometimes DBA have to deal with the wrong index case, it's the typical performance issue but not the syntax error, the sql server won't raise any message for this. but we should still find them and notify programmer, For example:
'we have a table as below:
'create table dbo.t1(
'    id int primary key
'   ,a1 char(3)
'   ,a2 char(10)
'   ,a3 char(30)
')
'Go
'create index ix_t1_a1 on dbo.t1(a3)

Dim SelectStatement As XElement = XElement.Parse( _
       Microsoft.SqlServer.SqlParser.Parser.Parser.Parse("select * from dbo.t1 where a2='USA'").Xml.ToString())
Dim TblStatement As XElement = XElement.Parse( _
       Microsoft.SqlServer.SqlParser.Parser.Parser.Parse("Create table dbo.t1....Create index...on dbo.t1(a3)").Xml.ToString())

Dim WhereColumns=From el as XElement in SelectStatement.XPathSelectElements(someXmlPath)
                               Select el.Attribute("ColumnName")
Dim IndexColumns=From el as XElement in TblStatement.XPathSelectElements(someXmlPath)
                               Select el.Attribute("ColumnName")
'do compare and return error list.

Is there any way that export scriptDom as Xml format?
Your suggestion is appreciated.

Sorry for late.

Answer 3

As far as I can see  this code give back something like this:

 

 

var parser = Parser.Parse("select * from a");
string x = parser.Xml;
Console.WriteLine(x);

 

result:

 

 

<SqlScript Location="((1,1),(1,16))">
 <!--select * from a-->
 <SqlBatch Location="((1,1), (1,16))">
 <!--select * from a-->
 <Errors />
 <SqlSelectStatement Location="((1,1),(1,16))">
  <!--select * from a-->
  <SqlSelectSpecification Location="((1,1),(1,16))">
  <!--select * from a-->
  <SqlQuerySpecification Location="((1,1),(1,16))">
   <!--select * from a-->
   <SqlSelectClause Location="((1,1),(1,9))" IsDistinct="False">
   <!--select *-->
   <SqlSelectStarExpression Location="((1,8),(1,9))">
    <!--*-->
   </SqlSelectStarExpression>
   </SqlSelectClause>
   <SqlFromClause Location="((1,10),(1,16))">
   <!--from a-->
   <SqlTableRefExpression Location="((1,15),(1,16))" ObjectIdentifier="a">
    <!--a-->
    <SqlObjectIdentifier Location="((1,15),(1,16))" ObjectName="a" IsMultiPartName="False" Count="1">
    <!--a-->
    <SqlIdentifier Location="((1,15),(1,16))" Value="a">
     <!--a-->
    </SqlIdentifier>
    </SqlObjectIdentifier>
   </SqlTableRefExpression>
   </SqlFromClause>
  </SqlQuerySpecification>
  </SqlSelectSpecification>
 </SqlSelectStatement>
 <Tokens>
  <Token location="((1,1), (1,7))" id="287" type="TOKEN_SELECT">select</Token>
  <Token location="((1,7), (1,8))" id="498" type="LEX_WHITE"> </Token>
  <Token location="((1,8), (1,9))" id="42" type="*">*</Token>
  <Token location="((1,9), (1,10))" id="498" type="LEX_WHITE"> </Token>
  <Token location="((1,10), (1,14))" id="242" type="TOKEN_FROM">from</Token>
  <Token location="((1,14), (1,15))" id="498" type="LEX_WHITE"> </Token>
  <Token location="((1,15), (1,16))" id="171" type="TOKEN_ID">a</Token>
 </Tokens>
 </SqlBatch>
</SqlScript>

 

This is an example for the new parser:

 

 

using (var sr = new StringReader("select * from a"))
   {
    IList<ParseError> errors;

    var fragment = parser2.Parse(sr, out errors); 
    if (fragment is TSqlFragment)
    {
     var sqlFragment = (TSqlFragment)fragment;
          
     foreach (TSqlParserToken tokenizedData in sqlFragment.ScriptTokenStream)
     {
      
      var serializer = new XmlSerializer(tokenizedData.GetType());
      var stream = new MemoryStream();
      XmlWriter xmlWriter = XmlWriter.Create(stream);

      if (xmlWriter != null)
      {
       serializer.Serialize(xmlWriter, tokenizedData);
       xmlWriter.Flush();
      }

      string xml = Encoding.GetEncoding("UTF-8").GetString(stream.ToArray());

      if (xmlWriter != null)
      {
       xmlWriter.Close();
      }
      stream.Close();
      Console.WriteLine(string.Format(xml));
     }
    }
    
   }

 

Result:

 

 

<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>Select</TokenType><Offset>0</Offset><Line>1</Line><Column>1</Column><Text>select</Text></TSqlParserToken>
<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>WhiteSpace</TokenType><Offset>6</Offset><Line>1</Line><Column>7</Column><Text> </Text></TSqlParserToken>
<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>Star</TokenType><Offset>7</Offset><Line>1</Line><Column>8</Column><Text>*</Text></TSqlParserToken>
<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>WhiteSpace</TokenType><Offset>8</Offset><Line>1</Line><Column>9</Column><Text> </Text></TSqlParserToken>
<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>From</TokenType><Offset>9</Offset><Line>1</Line><Column>10</Column><Text>from</Text></TSqlParserToken>
<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>WhiteSpace</TokenType><Offset>13</Offset><Line>1</Line><Column>14</Column><Text> </Text></TSqlParserToken>
<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>Identifier</TokenType><Offset>14</Offset><Line>1</Line><Column>15</Column><Text>a</Text></TSqlParserToken>
<?xml version="1.0" encoding="utf-8"?><TSqlParserToken xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><TokenType>EndOfFile</TokenType><Offset>15</Offset><Line>1</Line><Column>16</Column></TSqlParserToken>

 

As you can see you can only deserialize TSqlParserToken datatype, but this is not the same format like in my first sample . I think you can produce similar xml for your custom code. This is the better way if you would like to use scriptdom assembly.

 

However, I have tried to use Microsoft.SqlServer.SqlParser.dll and i have realized that is in in the GAC: C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.SqlParser\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.SqlParser.dll

I hope this help.

---

István Sáfár

Answer 4

Hi István Sáfár,

Thank you for the huge help.I'll do more research on SMO(Microsoft.SqlServer.Management.SqlParser.dll).

before the final solution is being found, I will tempororily use regex expression to resolve the missed SQLcommand that old parser doesn't recogize.

Answer 5

Hi UEHOO

You can get Xml from Microsoft.SqlServer.Management.SqlParser.dll, but it evolve some clever hacks. This is how I do it, it uses dynamics, meaning .Net 4 or later. The AccessPrivateWrapper is some code I found on the internet, so I won't take credit for that, but unfortunely I cannot remember the author

/Michael Søndergaard

private static string GetParseResultInXml( ParseResult result )
{
	dynamic wrapperScript = new AccessPrivateWrapper(result);
	dynamic o = wrapperScript.Script;
	dynamic wrapperScriptXml = new AccessPrivateWrapper(o);
	return wrapperScriptXml.Xml;
}

public static XDocument ParseContentToXml( string content, bool quotedIdentifierStatus)
{
	ParseResult result = Microsoft.SqlServer.Management.SqlParser.Parser.Parser.Parse(content, new ParseOptions("", quotedIdentifierStatus, DatabaseCompatibilityLevel.Version110, TransactSqlVersion.Version110));
	if (result.Errors.Count() != 0)
		return null;

	string xml = GetParseResultInXml(result);
	XDocument document = new XDocument();
	document.Add(XElement.Parse(xml));

	return document;
}

public class AccessPrivateWrapper : DynamicObject
{

    /// <summary>
    /// The object we are going to wrap
    /// </summary>
    readonly object _wrapped;

    /// <summary>
    /// Specify the flags for accessing members
    /// </summary>
    private const BindingFlags flags = BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public;

    /// <summary>
    /// Create a simple private wrapper
    /// </summary>
    public AccessPrivateWrapper(object o)
    {
        _wrapped = o;
    }

    /// <summary>
    /// Try invoking a method
    /// </summary>
    public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
    {
        var types = from a in args
                    select a.GetType();

        var method = _wrapped.GetType().GetMethod
            (binder.Name, flags, null, types.ToArray(), null);

        if (method == null)
            return base.TryInvokeMember(binder, args, out result);
        result = method.Invoke(_wrapped, args);
        return true;
    }

    /// <summary>
    /// Tries to get a property or field with the given name
    /// </summary>
    public override bool TryGetMember(System.Dynamic.GetMemberBinder binder, out object result)
    {
        //Try getting a property of that name
        var prop = _wrapped.GetType().GetProperty(binder.Name, flags);

        if (prop == null)
        {
            //Try getting a field of that name
            var fld = _wrapped.GetType().GetField(binder.Name, flags);
            if (fld != null)
            {
                result = fld.GetValue(_wrapped);
                return true;
            }
            return base.TryGetMember(binder, out result);
        }
        result = prop.GetValue(_wrapped, null);
        return true;
    }

    /// <summary>
    /// Tries to set a property or field with the given name
    /// </summary>
    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        var prop = _wrapped.GetType().GetProperty(binder.Name, flags);
        if (prop == null)
        {
            var fld = _wrapped.GetType().GetField(binder.Name, flags);
            if (fld != null)
            {
                fld.SetValue(_wrapped, value);
                return true;
            }
            return base.TrySetMember(binder, value);
        }
        prop.SetValue(_wrapped, value, null);
        return true;
    }

}