TSQL Parser

我们在进行TSQL开发或脚本检查时，经常因为代码过多，又不太可能花费太多时间反复检查，导致违反编码规范的脚本未被修正.

这种情况下，我们就需要通过程序，对脚本通过程序解析，尽量排除一些常见的，易于程序发现的问题。这时我们就需要对TSQL语法进行语义解析工作，众所周知这时一个非常困难的工程，还好微软提供了专门用于TSQL解析的类库。下面将简述两种常见的语句解析类库的使用方法

一、过去MSSQL2008R2之前可以通过Microsoft.SqlServer.SqlParser进行解析

引用dll见附件

使用方法类似

            dim SQL As String=“待解析SQL语句”
            Dim xml As String = ""
            '将sql语句解析为xml格式，保存到XBase对象中返回
            xml = Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(SQL).Xml.ToString()

            Dim el As XElement = XElement.Parse(xml)

然后解析细节就是通过这个XElement 解析对应的节点，来判定了

二、过去MSSQL2008R2之前可以通过Microsoft.SqlServer.SqlParser进行解析

     从MSSQL2012后就被MS放弃了之前的通过解析出xml的方式提供的脚本解析。转而采用更加繁琐但更细节的的Microsoft.SqlServer.TransactSql.ScriptDom类库用于TSQL解析工作，这个类库非常庞大几乎包含TSQL语法中的所有元素都进行了对象话，虽然非常繁琐，但是可以让我们更准确的解析到语句

其引用DLL在mssql2014中路径为(安装该版本的SSMS后即存在)：

C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll

以下代码都是基于这个库进行的开发

--将语句发给解析器，解析器反馈语句是否存在语法错误

Imports Microsoft.SqlServer.TransactSql.ScriptDom

Dim strSql As String=“待解析SQL语句”

Dim SqlParser As TSql120Parser = New TSql120Parser(False)
Dim parseErrors As IList(Of Microsoft.SqlServer.TransactSql.ScriptDom.ParseError)

Using tReader As System.IO.TextReader = New System.IO.StringReader(strSql)
            Dim result As TSqlFragment = SqlParser.Parse(tReader, parseErrors)

              '判定语法是否合法
            If parseErrors.Count > 0 Then
                Dim strOutError As String = ""
                For Each PError As ParseError In parseErrors
                    strOutError = strOutError & "Error: " & PError.Number & " Line: " & PError.Line & " Column: " & PError.Column & " Offset: " & PError.Offset & " Message: " & PError.Message & vbCrLf
                Next
                MessageBox.Show(strOutError)
                Exit Sub
            End If

End Using

--对每个batch/单个语句进行分别处理，并对不同的语句类型，一一解析我们需要的各种属性，下面简单写了一些脚本类型的解析方法

Dim SqlScript As TSqlScript = result

Dim fieldList As List(Of String) = New List(Of String)

 For Each sqlBatch As TSqlBatch In SqlScript.Batches

         sb.AppendLine("one batch")
         sb.AppendLine("---------------------------")
         sb.AppendLine("/*" & strSql.Substring(sqlBatch.StartOffset, sqlBatch.FragmentLength) & "*/")

          '解析当前batch中的所有独立语句

         For Each sqlStatement As TSqlStatement In sqlBatch.Statements

                   '根据不同类型进行分别解析

                  Select Case sqlStatement.GetType.Name.ToString()

                        Case "SelectStatement"

                            Exit Select

                        Case "UseStatement"
                            sb.AppendLine("UseStatement")
                            sb.AppendLine("/*" & strSql.Substring(sqlBatch.StartOffset, sqlBatch.FragmentLength) & "*/")
                            Dim sUS As UseStatement = DirectCast(sqlStatement, UseStatement)
                            sb.AppendLine("USE DB is " & sUS.DatabaseName.Value.ToString)
                            Exit Select
                        Case "CreateProcedureStatement"
                            sb.AppendLine("CreateProcedureStatement")
                            sb.AppendLine("/*" & strSql.Substring(sqlBatch.StartOffset, sqlBatch.FragmentLength) & "*/")
                            Dim sCPC As CreateProcedureStatement = DirectCast(sqlStatement, CreateProcedureStatement)
                            If sCPC.ProcedureReference.Name.SchemaIdentifier Is Nothing Then
                                sb.AppendLine("this proc name is " & "[dbo].[" & sCPC.ProcedureReference.Name.BaseIdentifier.Value.ToString & "]")
                            Else
                                sb.AppendLine("this proc name is " & "[" & sCPC.ProcedureReference.Name.SchemaIdentifier.Value.ToString & "].[" & sCPC.ProcedureReference.Name.BaseIdentifier.Value.ToString & "]")
                            End If

                            Exit Select

                        Case "CreateTableStatement"

                            Dim sTB As CreateTableStatement = DirectCast(sqlStatement, CreateTableStatement)
                            '获取table name和schema
                            Dim sTBName As String = ""
                            If sTB.SchemaObjectName.Identifiers.Count > 0 Then
                                For Each sio As Identifier In sTB.SchemaObjectName.Identifiers
                                    sTBName = sTBName & ".[" & sio.Value.ToString & "]"
                                Next
                                sb.AppendLine("this table name is " & sTBName.Substring(1, sTBName.Length - 1))
                            End If

                            Exit Select

                        Case "CreateIndexStatement"

                            sb.AppendLine("CreateIndexStatement")
                            sb.AppendLine("/*" & strSql.Substring(sqlBatch.StartOffset, sqlBatch.FragmentLength) & "*/")
                            Dim sIX As CreateIndexStatement = DirectCast(sqlStatement, CreateIndexStatement)

                             '获取Index name
                            sb.AppendLine("the index name is " & sIX.Name.Value.ToString)

                             '判定是否为Unique 
                            If sIX.Unique = True Then
                                sb.AppendLine("this index is unique")
                            End If

                             ‘获取with中的参数，这里仅对FillFactor进行判定
                            For Each sio As IndexExpressionOption In sIX.IndexOptions
                                If sio.OptionKind.ToString = "FillFactor" Then
                                    sb.AppendLine("this index's FillFactor is " & DirectCast(sio.Expression, IntegerLiteral).Value.ToString)
                                End If
                            Next

                            Exit Select

                        Case Else
                            Exit Select

                   End Select

          Next

 Next