Generate a Query using Microsoft.SqlServer.TransactSql.ScriptDom

Question

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!

Ed Elliott · Accepted Answer · 2015-11-03 08:35:22Z

2

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.

answered Nov 3, 2015 at 8:35

Ed Elliott

6,87420 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate a Query using Microsoft.SqlServer.TransactSql.ScriptDom

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related