Provide an option in the SqlNNNScriptGenerator classes to preserve comments

[arvindshmicrosoft]

Is your feature request related to a problem? Please describe. Some use cases for the Sql*ScriptGenerator classes are actually for code "pretty printing" / formatting. However, using Sql150ScriptGenerator to re-generate the SQL text when provided with an input TSqlFragment, leads to comments (single-line and multi-line) both being excluded from the output.

Describe the solution you'd like Provide an PreserveComments property in SqlScriptGeneratorOptions with the default being true in the 160 version, and false in the 150 or lower versions for backward compat.

Describe alternatives you've considered Token re-writing: substituting a faux print statement instead of the comment token is a crude workaround. It fails in case the comment is a trailing suffix of a line within a multi-line T-SQL statement.

[dmaloo]

Would really appreciate if this issue was fixed, it will help greatly with using scriptdom to fix code issues. thank you.

[tutuhub]

This fix would be a huge help! Not only for me but many others. Thank you.

[DBojsen]

Completely agree that we need an option to preserve comments when using the script generator.

[ay-azara]

Just in case someone wants to tackle this the filtering seems to occur here. https://github.com/microsoft/SqlScriptDOM/blob/714ef041979cd2625db2860d950605dba2b1684d/SqlScriptDom/Parser/TSql/TSqlTokenFilter.cs#L40-L43

The only place I found TSqlTokenFilter called was SqlScriptDom/Parser/TSql/TSql80ParserBaseInternal.cs which is inherited by all the other Parsers so this seems to go deeper than ScriptGenerator options.

EDIT: I guess if ScriptGenerator controls the creation of the parser an option could be created for TsqlNNNParser but I can't figure out where that class is getting called.

So this

-- This is a comment
SELECT * FROM mytable

results in

$Results.ParsedObjects.Batches[0].ScriptTokenStream[0] // Value: SingleLineComment
$Results.ParsedObjects.Batches[0].ScriptTokenStream[1] // Value: Whitespace Newline
$Results.ParsedObjects.Batches[0].ScriptTokenStream[2] // Value: SELECT
$Results.ParsedObjects.Batches[0].FirstTokenIndex // Value: 2

Comments would need to be excluded from semicolon adding and it might cause some tests to fail if they aren't expecting comments.

Fiddled around with Token rewriting but unlike Arvind I didn't have any luck. Modifying TokenType or Text on ScriptTokenStream tokens and passing them into the Generator produced the same result. TsqlFragement.Batches[0].Statements[0].Expression.Value can change some part of the statement but not the keyword. UpdateTokenInfo methods are internal.

[JerryNixon]

Without this, most ScriptDom use cases are dead in the water.