Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sqlparser parseresult - How do you parse? Expand / Collapse
Author
Message
Posted Friday, July 1, 2011 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 7, 2011 12:00 PM
Points: 11, Visits: 27
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.
Post #1135198
Posted Friday, July 1, 2011 11:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,962, Visits: 32,507
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1135273
Posted Saturday, July 2, 2011 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 7, 2011 12:00 PM
Points: 11, Visits: 27
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.
Post #1135495
Posted Saturday, July 2, 2011 11:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,962, Visits: 32,507
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1135533
Posted Monday, July 4, 2011 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 7, 2011 12:00 PM
Points: 11, Visits: 27
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.
Post #1135954
Posted Tuesday, July 5, 2011 6:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,962, Visits: 32,507
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' )

[code]
--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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1136408
Posted Tuesday, July 5, 2011 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 7, 2011 12:00 PM
Points: 11, Visits: 27
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.
Post #1136439
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse