July 1, 2011 at 10:41 am
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.
July 1, 2011 at 11:48 am
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
July 2, 2011 at 7:15 am
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.
July 2, 2011 at 11:32 am
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
July 4, 2011 at 7:18 am
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.
July 5, 2011 at 6:17 am
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
July 5, 2011 at 7:02 am
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