sqlparser parseresult - How do you parse?

  • I was looking for some sample code to figure out how to effectively use some of the classes in the Microsoft.SqlServer.Management.SqlParser namespace. But to my astonishment, after googling for two entire days, couldn't find even one single sample code that shows what to do with the ParseResult instance returned by the Parse() method. MSDN and Technet both have only the definitions for these classes and methods. But not one single example of how to use them.

    Here is what I am trying to do. I am trying to design an User interface (similar to the SSMS query designer) when a user can look at the query graphically (again, similar to SSMS), modify the query graphically or directly in sql text and execute it. Here is the sql for AdventureWorks View object "vEmployee" for example.

    SELECT e.EmployeeID, c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, e.Title AS JobTitle, c.Phone, c.EmailAddress, c.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, cr.Name AS CountryRegionName, c.AdditionalContactInfo

    FROM HumanResources.Employee AS e INNER JOIN

    Person.Contact AS c ON c.ContactID = e.ContactID INNER JOIN

    HumanResources.EmployeeAddress AS ea ON e.EmployeeID = ea.EmployeeID INNER JOIN

    Person.Address AS a ON ea.AddressID = a.AddressID INNER JOIN

    Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN

    Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode

    Using SqlParser or the DependencyTree classes in SqlServer.Management namespaces, how can I parse the tokens out of the View. I need to be able to individually identify the columns, tables, join types, aliases, where clause conditions etc. I know all these information is available as tokens somewhere in either the View object or on the Database. I was able to retrieve all the columns and the dependant table names by walking dependencytree. But I can't figure out how to extract the other items. Without all the items, I won't be able to reconstruct the sql when a user graphically modifies the query. I can really use some sample code (C#, VB.NET, doesn't matter).

    Babu.

  • Babu thanks for giving me something new and interesting to play with; being able to parse a statement has a lot of possibilities.

    i found an example in german here: http://olafhelper.over-blog.de/article-34700638.html

    i created a simple buttona dn put this specific code underneath it.

    what you want to look at is the tokens...i see how different tokens are used to identify columns, tables, even whitespace and comments, everything in the query is chopped up into something.

    i see so many possibilities witht his...wow.

    Private Sub btnParse_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParse.Click

    Dim sql As String

    Dim script As Microsoft.SqlServer.SqlParser.SqlCodeDom.SqlScript

    Dim stringBuilder As New System.Text.StringBuilder

    Dim token As Microsoft.SqlServer.SqlParser.Parser.Token

    sql = "-- Erstes Statement völlig Valide, aber mies formatiert " & vbNewLine & _

    "select [AddressID] " & _

    ", [AddressLine1] " & _

    ",[AddressLine2] " & _

    ", [City] " & _

    "fRoM [AdventureWorks].[Person].[Address] " & _

    "whERe PostalCode = '98011';" & vbNewLine & _

    "GO;" & vbNewLine & _

    "-- Zweites Statement: So strunz fehlerhaft " & vbNewLine & _

    "-- das es schon weh tut; Format auch nicht besser " & vbNewLine & _

    "select * " & _

    "FRoM TabelleA oder TabelleB " & _

    "where Ja = Vielleicht <> Nein;"

    sql = " " & vbCrLf

    sql = sql & "SELECT e.EmployeeID, c.Title, c.FirstName, c.MiddleName, c.LastName, c.Suffix, e.Title AS JobTitle, c.Phone, c.EmailAddress, c.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, cr.Name AS CountryRegionName, c.AdditionalContactInfo " & vbCrLf

    sql = sql & "FROM HumanResources.Employee AS e INNER JOIN " & vbCrLf

    sql = sql & "Person.Contact AS c ON c.ContactID = e.ContactID INNER JOIN " & vbCrLf

    sql = sql & "HumanResources.EmployeeAddress AS ea ON e.EmployeeID = ea.EmployeeID INNER JOIN " & vbCrLf

    sql = sql & "Person.Address AS a ON ea.AddressID = a.AddressID INNER JOIN " & vbCrLf

    sql = sql & "Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN " & vbCrLf

    sql = sql & "Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode " & vbCrLf

    script = Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(sql)

    System.Console.WriteLine("Anzahl Batches: " & script.Batches.Count.ToString)

    System.Console.WriteLine("Anzahl Fehler: " & script.Errors.Count.ToString)

    For Each token In script.Tokens

    'Debug.Print("token ID [" & token.Id & "] ;Token Type [" & token.Type & "]" & " xml [" & token.Xml & "]")

    Debug.Print("token xml [" & token.Xml & "]")

    Select Case token.Id

    Case 40, 41, 59, 61

    'As separators () =, which may be without Spaces

    stringBuilder.Append(token.Text.Trim)

    Case 42, 171

    'Fields, the only trim

    stringBuilder.Append(token.Text.Trim)

    Case 44, 498 '498 = "LEX_WHITE", vbCrLf i think

    'Empty field, where only max. a Space

    stringBuilder.Append(token.Text.Trim & " ")

    Case 242, 287, 307 '287 = "TOKEN_SELECT"

    'SQL clauses wie SELECT FROM WHERE

    'The next in line as UPPER

    stringBuilder.Append(vbNewLine & token.Text.ToUpper(System.Globalization.CultureInfo.CurrentCulture))

    Case 499 'Type: "LEX_END_OF_LINE_COMMENT"

    'Comments, and no unnecessary spaces in new row

    stringBuilder.Append(vbNewLine & token.Text.Trim.Replace(" ", " "))

    Case 501

    'GO batch separators, always in new row

    stringBuilder.Append(vbNewLine & token.Text.Trim)

    Case Else

    'Everything else return unchanged

    stringBuilder.Append(token.Text)

    End Select

    Next

    'System.Console.WriteLine(stringBuilder.ToString)

    'System.Console.WriteLine("Press key to continue")

    'System.Console.ReadLine()

    End Sub

    anyway, for reference, your specific Adventureworks is tokenized into this:

    token xml [<Token location="((1,1), (2,1))" id="498" type="LEX_WHITE"> \r</Token>]

    token xml [<Token location="((2,1), (2,7))" id="287" type="TOKEN_SELECT">SELECT</Token>]

    token xml [<Token location="((2,7), (2,8))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,8), (2,9))" id="171" type="TOKEN_ID">e</Token>]

    token xml [<Token location="((2,9), (2,10))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,10), (2,20))" id="171" type="TOKEN_ID">EmployeeID</Token>]

    token xml [<Token location="((2,20), (2,21))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,21), (2,22))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,22), (2,23))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,23), (2,24))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,24), (2,29))" id="171" type="TOKEN_ID">Title</Token>]

    token xml [<Token location="((2,29), (2,30))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,30), (2,31))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,31), (2,32))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,32), (2,33))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,33), (2,42))" id="171" type="TOKEN_ID">FirstName</Token>]

    token xml [<Token location="((2,42), (2,43))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,43), (2,44))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,44), (2,45))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,45), (2,46))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,46), (2,56))" id="171" type="TOKEN_ID">MiddleName</Token>]

    token xml [<Token location="((2,56), (2,57))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,57), (2,58))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,58), (2,59))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,59), (2,60))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,60), (2,68))" id="171" type="TOKEN_ID">LastName</Token>]

    token xml [<Token location="((2,68), (2,69))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,69), (2,70))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,70), (2,71))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,71), (2,72))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,72), (2,78))" id="171" type="TOKEN_ID">Suffix</Token>]

    token xml [<Token location="((2,78), (2,79))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,79), (2,80))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,80), (2,81))" id="171" type="TOKEN_ID">e</Token>]

    token xml [<Token location="((2,81), (2,82))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,82), (2,87))" id="171" type="TOKEN_ID">Title</Token>]

    token xml [<Token location="((2,87), (2,88))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,88), (2,90))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((2,90), (2,91))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,91), (2,99))" id="171" type="TOKEN_ID">JobTitle</Token>]

    token xml [<Token location="((2,99), (2,100))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,100), (2,101))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,101), (2,102))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,102), (2,103))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,103), (2,108))" id="171" type="TOKEN_ID">Phone</Token>]

    token xml [<Token location="((2,108), (2,109))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,109), (2,110))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,110), (2,111))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,111), (2,112))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,112), (2,124))" id="171" type="TOKEN_ID">EmailAddress</Token>]

    token xml [<Token location="((2,124), (2,125))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,125), (2,126))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,126), (2,127))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,127), (2,128))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,128), (2,142))" id="171" type="TOKEN_ID">EmailPromotion</Token>]

    token xml [<Token location="((2,142), (2,143))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,143), (2,144))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,144), (2,145))" id="171" type="TOKEN_ID">a</Token>]

    token xml [<Token location="((2,145), (2,146))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,146), (2,158))" id="171" type="TOKEN_ID">AddressLine1</Token>]

    token xml [<Token location="((2,158), (2,159))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,159), (2,160))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,160), (2,161))" id="171" type="TOKEN_ID">a</Token>]

    token xml [<Token location="((2,161), (2,162))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,162), (2,174))" id="171" type="TOKEN_ID">AddressLine2</Token>]

    token xml [<Token location="((2,174), (2,175))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,175), (2,176))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,176), (2,177))" id="171" type="TOKEN_ID">a</Token>]

    token xml [<Token location="((2,177), (2,178))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,178), (2,182))" id="171" type="TOKEN_ID">City</Token>]

    token xml [<Token location="((2,182), (2,183))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,183), (2,184))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,184), (2,186))" id="171" type="TOKEN_ID">sp</Token>]

    token xml [<Token location="((2,186), (2,187))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,187), (2,191))" id="171" type="TOKEN_ID">Name</Token>]

    token xml [<Token location="((2,191), (2,192))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,192), (2,194))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((2,194), (2,195))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,195), (2,212))" id="171" type="TOKEN_ID">StateProvinceName</Token>]

    token xml [<Token location="((2,212), (2,213))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,213), (2,214))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,214), (2,215))" id="171" type="TOKEN_ID">a</Token>]

    token xml [<Token location="((2,215), (2,216))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,216), (2,226))" id="171" type="TOKEN_ID">PostalCode</Token>]

    token xml [<Token location="((2,226), (2,227))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,227), (2,228))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,228), (2,230))" id="171" type="TOKEN_ID">cr</Token>]

    token xml [<Token location="((2,230), (2,231))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,231), (2,235))" id="171" type="TOKEN_ID">Name</Token>]

    token xml [<Token location="((2,235), (2,236))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,236), (2,238))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((2,238), (2,239))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,239), (2,256))" id="171" type="TOKEN_ID">CountryRegionName</Token>]

    token xml [<Token location="((2,256), (2,257))" id="44" type=",">,</Token>]

    token xml [<Token location="((2,257), (2,258))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((2,258), (2,259))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((2,259), (2,260))" id="46" type=".">.</Token>]

    token xml [<Token location="((2,260), (2,281))" id="171" type="TOKEN_ID">AdditionalContactInfo</Token>]

    token xml [<Token location="((2,281), (3,1))" id="498" type="LEX_WHITE"> \r</Token>]

    token xml [<Token location="((3,1), (3,5))" id="242" type="TOKEN_FROM">FROM</Token>]

    token xml [<Token location="((3,5), (3,6))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((3,6), (3,20))" id="171" type="TOKEN_ID">HumanResources</Token>]

    token xml [<Token location="((3,20), (3,21))" id="46" type=".">.</Token>]

    token xml [<Token location="((3,21), (3,29))" id="171" type="TOKEN_ID">Employee</Token>]

    token xml [<Token location="((3,29), (3,30))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((3,30), (3,32))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((3,32), (3,33))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((3,33), (3,34))" id="171" type="TOKEN_ID">e</Token>]

    token xml [<Token location="((3,34), (3,35))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((3,35), (3,40))" id="150" type="TOKEN_INNER">INNER</Token>]

    token xml [<Token location="((3,40), (3,41))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((3,41), (3,45))" id="155" type="TOKEN_JOIN">JOIN</Token>]

    token xml [<Token location="((3,45), (4,1))" id="498" type="LEX_WHITE"> \r</Token>]

    token xml [<Token location="((4,1), (4,7))" id="171" type="TOKEN_ID">Person</Token>]

    token xml [<Token location="((4,7), (4,8))" id="46" type=".">.</Token>]

    token xml [<Token location="((4,8), (4,15))" id="171" type="TOKEN_ID">Contact</Token>]

    token xml [<Token location="((4,15), (4,16))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,16), (4,18))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((4,18), (4,19))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,19), (4,20))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((4,20), (4,21))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,21), (4,23))" id="269" type="TOKEN_ON">ON</Token>]

    token xml [<Token location="((4,23), (4,24))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,24), (4,25))" id="171" type="TOKEN_ID">c</Token>]

    token xml [<Token location="((4,25), (4,26))" id="46" type=".">.</Token>]

    token xml [<Token location="((4,26), (4,35))" id="171" type="TOKEN_ID">ContactID</Token>]

    token xml [<Token location="((4,35), (4,36))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,36), (4,37))" id="61" type="=">=</Token>]

    token xml [<Token location="((4,37), (4,38))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,38), (4,39))" id="171" type="TOKEN_ID">e</Token>]

    token xml [<Token location="((4,39), (4,40))" id="46" type=".">.</Token>]

    token xml [<Token location="((4,40), (4,49))" id="171" type="TOKEN_ID">ContactID</Token>]

    token xml [<Token location="((4,49), (4,50))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,50), (4,55))" id="150" type="TOKEN_INNER">INNER</Token>]

    token xml [<Token location="((4,55), (4,56))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((4,56), (4,60))" id="155" type="TOKEN_JOIN">JOIN</Token>]

    token xml [<Token location="((4,60), (5,1))" id="498" type="LEX_WHITE"> \r</Token>]

    token xml [<Token location="((5,1), (5,15))" id="171" type="TOKEN_ID">HumanResources</Token>]

    token xml [<Token location="((5,15), (5,16))" id="46" type=".">.</Token>]

    token xml [<Token location="((5,16), (5,31))" id="171" type="TOKEN_ID">EmployeeAddress</Token>]

    token xml [<Token location="((5,31), (5,32))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,32), (5,34))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((5,34), (5,35))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,35), (5,37))" id="171" type="TOKEN_ID">ea</Token>]

    token xml [<Token location="((5,37), (5,38))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,38), (5,40))" id="269" type="TOKEN_ON">ON</Token>]

    token xml [<Token location="((5,40), (5,41))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,41), (5,42))" id="171" type="TOKEN_ID">e</Token>]

    token xml [<Token location="((5,42), (5,43))" id="46" type=".">.</Token>]

    token xml [<Token location="((5,43), (5,53))" id="171" type="TOKEN_ID">EmployeeID</Token>]

    token xml [<Token location="((5,53), (5,54))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,54), (5,55))" id="61" type="=">=</Token>]

    token xml [<Token location="((5,55), (5,56))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,56), (5,58))" id="171" type="TOKEN_ID">ea</Token>]

    token xml [<Token location="((5,58), (5,59))" id="46" type=".">.</Token>]

    token xml [<Token location="((5,59), (5,69))" id="171" type="TOKEN_ID">EmployeeID</Token>]

    token xml [<Token location="((5,69), (5,70))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,70), (5,75))" id="150" type="TOKEN_INNER">INNER</Token>]

    token xml [<Token location="((5,75), (5,76))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((5,76), (5,80))" id="155" type="TOKEN_JOIN">JOIN</Token>]

    token xml [<Token location="((5,80), (6,1))" id="498" type="LEX_WHITE"> \r</Token>]

    token xml [<Token location="((6,1), (6,7))" id="171" type="TOKEN_ID">Person</Token>]

    token xml [<Token location="((6,7), (6,8))" id="46" type=".">.</Token>]

    token xml [<Token location="((6,8), (6,15))" id="171" type="TOKEN_ID">Address</Token>]

    token xml [<Token location="((6,15), (6,16))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,16), (6,18))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((6,18), (6,19))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,19), (6,20))" id="171" type="TOKEN_ID">a</Token>]

    token xml [<Token location="((6,20), (6,21))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,21), (6,23))" id="269" type="TOKEN_ON">ON</Token>]

    token xml [<Token location="((6,23), (6,24))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,24), (6,26))" id="171" type="TOKEN_ID">ea</Token>]

    token xml [<Token location="((6,26), (6,27))" id="46" type=".">.</Token>]

    token xml [<Token location="((6,27), (6,36))" id="171" type="TOKEN_ID">AddressID</Token>]

    token xml [<Token location="((6,36), (6,37))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,37), (6,38))" id="61" type="=">=</Token>]

    token xml [<Token location="((6,38), (6,39))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,39), (6,40))" id="171" type="TOKEN_ID">a</Token>]

    token xml [<Token location="((6,40), (6,41))" id="46" type=".">.</Token>]

    token xml [<Token location="((6,41), (6,50))" id="171" type="TOKEN_ID">AddressID</Token>]

    token xml [<Token location="((6,50), (6,51))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,51), (6,56))" id="150" type="TOKEN_INNER">INNER</Token>]

    token xml [<Token location="((6,56), (6,57))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((6,57), (6,61))" id="155" type="TOKEN_JOIN">JOIN</Token>]

    token xml [<Token location="((6,61), (7,1))" id="498" type="LEX_WHITE"> \r</Token>]

    token xml [<Token location="((7,1), (7,7))" id="171" type="TOKEN_ID">Person</Token>]

    token xml [<Token location="((7,7), (7,8))" id="46" type=".">.</Token>]

    token xml [<Token location="((7,8), (7,21))" id="171" type="TOKEN_ID">StateProvince</Token>]

    token xml [<Token location="((7,21), (7,22))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,22), (7,24))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((7,24), (7,25))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,25), (7,27))" id="171" type="TOKEN_ID">sp</Token>]

    token xml [<Token location="((7,27), (7,28))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,28), (7,30))" id="269" type="TOKEN_ON">ON</Token>]

    token xml [<Token location="((7,30), (7,31))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,31), (7,33))" id="171" type="TOKEN_ID">sp</Token>]

    token xml [<Token location="((7,33), (7,34))" id="46" type=".">.</Token>]

    token xml [<Token location="((7,34), (7,49))" id="171" type="TOKEN_ID">StateProvinceID</Token>]

    token xml [<Token location="((7,49), (7,50))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,50), (7,51))" id="61" type="=">=</Token>]

    token xml [<Token location="((7,51), (7,52))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,52), (7,53))" id="171" type="TOKEN_ID">a</Token>]

    token xml [<Token location="((7,53), (7,54))" id="46" type=".">.</Token>]

    token xml [<Token location="((7,54), (7,69))" id="171" type="TOKEN_ID">StateProvinceID</Token>]

    token xml [<Token location="((7,69), (7,70))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,70), (7,75))" id="150" type="TOKEN_INNER">INNER</Token>]

    token xml [<Token location="((7,75), (7,76))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((7,76), (7,80))" id="155" type="TOKEN_JOIN">JOIN</Token>]

    token xml [<Token location="((7,80), (8,1))" id="498" type="LEX_WHITE"> \r</Token>]

    token xml [<Token location="((8,1), (8,7))" id="171" type="TOKEN_ID">Person</Token>]

    token xml [<Token location="((8,7), (8,8))" id="46" type=".">.</Token>]

    token xml [<Token location="((8,8), (8,21))" id="171" type="TOKEN_ID">CountryRegion</Token>]

    token xml [<Token location="((8,21), (8,22))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((8,22), (8,24))" id="203" type="TOKEN_AS">AS</Token>]

    token xml [<Token location="((8,24), (8,25))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((8,25), (8,27))" id="171" type="TOKEN_ID">cr</Token>]

    token xml [<Token location="((8,27), (8,28))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((8,28), (8,30))" id="269" type="TOKEN_ON">ON</Token>]

    token xml [<Token location="((8,30), (8,31))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((8,31), (8,33))" id="171" type="TOKEN_ID">cr</Token>]

    token xml [<Token location="((8,33), (8,34))" id="46" type=".">.</Token>]

    token xml [<Token location="((8,34), (8,51))" id="171" type="TOKEN_ID">CountryRegionCode</Token>]

    token xml [<Token location="((8,51), (8,52))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((8,52), (8,53))" id="61" type="=">=</Token>]

    token xml [<Token location="((8,53), (8,54))" id="498" type="LEX_WHITE"> </Token>]

    token xml [<Token location="((8,54), (8,56))" id="171" type="TOKEN_ID">sp</Token>]

    token xml [<Token location="((8,56), (8,57))" id="46" type=".">.</Token>]

    token xml [<Token location="((8,57), (8,74))" id="171" type="TOKEN_ID">CountryRegionCode</Token>]

    token xml [<Token location="((8,74), (9,1))" id="498" type="LEX_WHITE"> \r</Token>]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell,

    That was an excellent example. One problem though. I can't find any of the assemblies you are using, on my installation. I have VS2010 with Sql Server 2008 R2 running on a Windows 7 Enterprise machine. I have similar SMO assemblies under Microsoft.SqlServer.Management namespace. Also, I have some more related assemblies under Microsoft.Data namespace installed with the Database edition of VS 2010. Even though there are SqlParser, Token, and SqlScript classes in these namespaces, they don't seem to have the same methods or properties as the ones you are using. Example, the Scripter class does not seem to have a token collection. So, I can't disect the script to find the tokens. Can you help me on this please?

    Also, you sound like a smart SQL expert. Can you figure out how to do the exact same thing you did but using the Management.SMO namespace and Management.SqlParser namespace? Thanks a lot again.

    Babu.

  • yeah it was a little work to find;

    all this work is from a Win7/64 bit OS.

    my example assumes you installed SQL Server 2008; although i installed 64 bit, the specific dll was actually in

    C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder.

    i'd expect yours to be the same but in C:\Program Files (x86)\Microsoft SQL Server\150?\ or \105? sub folder for R2

    i copied Microsoft.SqlServer.SqlParser.dll from that directory to my .\bin folder of a new project, and added a reference in my vs2008 project to that dll. i converted that project to VS 2010 and it works fine as well, but that'd be expected.

    the code i pasted should then work fine.

    I'm no expert in SMO, but once i know what a dll is supposed to do, it's seems pretty straight forward to look at.

    I thought the tokenization would be different myself; i'm looking at some other examples now,a nd wll report any significant results; for example, it seemed that if i could tokenize everything, i could more easily reformat a query, like put each table in the query on a separate line with it's FROM/JOIN operators, or put each column on it's own like, but it's not obvious to me at that detailed level of tokenization...

    you can get something very similar from the xml of an execution plan, but can you tell me what exactly you are trying to do?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell,

    I am using the 64bit version too. But it is R2. Under the folder path you mentioned, most of the assemblies are under Microsoft.SqlServer.Management namespace. In other words, no Microsoft.SqlServer.SqlParser name spaces. Like I said before, I did find SqlParser under the Microsoft.SqlServer.Management namespace. Also, there is Microsoft.Data.Schema.ScriptDom namespace under VS2010. There are some parser and scripting classes in there too. But those classes are built differently than the one in your sample code. I still can't figure out how to tokenize them.

    What I am trying to do here is, when the user selects a View or Stored procedures from a ListBox or a Tree, a Grid is populated in another pane with all the columns and the related tables including aliases, all the where conditions, sorting and grouping as one row per column. The user then, can optionally drag and drop more columns from other tables, if required, add/edit the where clause, sort order etc using the graphical interface. When they hit a button "Execute", I need to be able to reconstruct the entire script from the items in the grid. Very similar to SSMS query builder or View Builder interface.

  • OK Babu, say you've presented a list of views in your GUI, and the end user selected one of them.

    i don't think you need any other parsing DLL at all, all you need is the results of a couple of queries from the server.

    the list of columns that are part of the view would simply be this, right?

    and here are all the items that have FK references to the underlying tables that are part of my view.

    SELECT

    objz.name AS TableOrViewName,

    colz.name AS ColumnName,

    colz.column_id as ColumnOrder,

    TYPE_NAME(system_type_id) as DataType

    --colz.*

    FROM sys.columns colz

    INNER JOIN sys.objects objz ON colz.object_id = objz.object_id

    WHERE objz.name = 'VW_CITYCOUNTYSTATE'

    AND SCHEMA_NAME(objz.SCHEMA_ID) = 'dbo'

    now if you want items that reference the underlying tables of the views, so you can match foreign keys, i think it would be something like this:

    --the underlying tables of the view

    select object_name(referenced_major_id),* from sys.sql_dependencies depz

    inner join sys.objects objz

    on depz.object_id = objz.object_id

    WHERE objz.name = 'VW_CITYCOUNTYSTATE'

    AND SCHEMA_NAME(objz.SCHEMA_ID) = 'dbo'

    --items that my view references via underlying foreign keys in underlying tables

    SELECT *

    FROM sys.foreign_keys conz

    INNER JOIN sys.foreign_key_columns colz

    ON conz.object_id = colz.constraint_object_id

    WHERE conz.parent_object_id IN ( SELECT referenced_major_id FROM sys.sql_dependencies depz

    INNER JOIN sys.objects objz

    ON depz.object_id = objz.object_id

    WHERE objz.name = 'VW_CITYCOUNTYSTATE'

    AND SCHEMA_NAME(objz.SCHEMA_ID) = 'dbo' )

    --items that reference my VIEW via underlying FOREIGN keys FROM underlying tables

    SELECT *

    FROM sys.foreign_keys conz

    INNER JOIN sys.foreign_key_columns colz

    ON conz.object_id = colz.constraint_object_id

    WHERE conz.referenced_object_id IN ( SELECT referenced_major_id FROM sys.sql_dependencies depz

    INNER JOIN sys.objects objz

    ON depz.object_id = objz.object_id

    WHERE objz.name = 'VW_CITYCOUNTYSTATE'

    AND SCHEMA_NAME(objz.SCHEMA_ID) = 'dbo' )

    [/ode]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell,

    I was hoping I could do this all in .NET managed code using some Microsoft.SqlServer.Management namespace. But I guess it is either not possible or way too complicated to achieve.

    You have been great help. I thank you for all your time and suggestions. I will try to build on your suggestions into my application.

    Babu.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply