Generate a Query using Microsoft.SqlServer.TransactSql.ScriptDom

It seems ScriptDom has a complex architecture. Since there is no documentation about ScriptDom, do you know how can generate a joined query like this using ScriptDom types?

SELECT c.Title as ColumnTitle, t.Title as TableTitle, o.Title as OwnerTitle FROM info.iColumns c 
    JOIN info.iTables t ON c.TableRef = t.Id 
    JOIN info.iOwners o ON t.OwnerRef = o.Id 
    JOIN info.iTablesKey k ON k.TableRef = t.Id
    WHERE t.DisplayIndex = 1

I want to create a simple query generator for my report builder and I don't know how to build JOIN statements. I have checked this blog post but the generated xml for object graph is very complex!

Solution

The easiest way to figure out what you need to add is to do the reverse - start with your t-sql, use the parser to parse it and create a TSqlScript which you then pass to a class that implements TSqlFragmentVisitor.

In your class that implements TSqlFragmentVisitor do an override on Visit(TSqlScript node) - put a breakpoint in there and then you can explore what objects make up your query.

Building an AST using the ScriptDom is quite challenging but rewarding in the end :)

For example on how to use the scriptdom see the scriptdom section of this:

https://the.agilesql.club/Blogs/Ed-Elliott/DacFx-Create-tSQLt-Tests-From-A-Dacpac

I don't have an exact example of your use case to hand.