If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

Using the ScriptDom without losing comments

The ScriptDom is cool, it lets you parse T-SQL, play around with the AST (tree of statements) and then generate T-SQL again (probably in a different format etc). This is really cool but if you round-trip the T-SQL into an AST and back you lose some important information like comments so, if you do something like:

var original = @"create procedure [dbo].[get_employee](@employee_id int) as --ssssss

select 100;

select * from a a1 where a1.a like ‘%fff%’;
var parser = new TSql120Parser(false);

IList errors;
var fragment = parser.Parse(new StringReader(original), out errors);

var outputScript = new StringBuilder();

var generator = new Sql120ScriptGenerator();
generator.GenerateScript(fragment, new StringWriter(outputScript));

What you would end up with is instead of:

create procedure [dbo].[get_employee](@employee_id int) as --ssssss

select 100;

select * from a a1 where a1.a like ‘%fff%’;

You get:

CREATE PROCEDURE [dbo].[get_employee] @employee_id INT AS SELECT 100; SELECT * FROM a AS a1 WHERE a1.a LIKE '%fff%';

So if you wanted to do something like change all the keywords to upper-case it would be really simple but you would lose the comments which I know some people are quite attached to.

To get around this there are two ways that I can think of, if anyone has another way please shout!

Approach 1 - replace just the parts you want to

When you parse the t-sql you get back a TSqlFragment, all of the ScriptDom statements like CreateProcedureStatement inherit from TSqlFragment (by way of a TSqlStatement and probably a whole load of other things) what this means is that as well as being able to maniplulate the contents of the statement, we get the offset into the start of the string that was parsed and the length of the fragment. So for example if we wanted to change the type of the parameter, we would use the ScriptDom to find the parameter, we would then have the StartOffset and the Fragment length. Using those two values we can do a replace on the sub-string with whatever we want to put in its place.

Approach 2 - modify the TSqlToken on the fly

The second approach is to get the parser to parse the script and create the TSqlFragment but instead of using the AST and modifying the TSqlStatements, you can iterate through all of the tokens and modify the ones you don’t like. For instance if you wanted to change:

create procedure abc as select 100;


alter procedure abc as select 'ABC';

You could simply do a find and replace on “create procedure” but what about cases like “create /*frrrrrrr*/ procedure” or something else entirely, manually parsing it isn’t trivial so instead we can use my second approach which is to modify the tokens. In this case to change the create into an alter we can do something like:

bool haveCreate = false; var output = new StringBuilder();

foreach (var token in fragment.ScriptTokenStream)
if (!haveCreate && token.TokenType == TSqlTokenType.Create)
var alterToken = new TSqlParserToken(TSqlTokenType.Alter, “alter”);
haveCreate = true;
return output.ToString();

So “round-tripping” t-sql via a ScriptDom parser and generator does lose some of the content which may be useful but all is not lost, here are a couple of approaches, if anyone has some more please share!


* indicates required

Please select all the ways you would like to hear from Agile Sql Club:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.