Enhacement to Normlize Queries That are Called with sp_executesql

[imajaydwivedi]

I have imported the ScriptDom assemblies into my GitHub project SQLMonitor.

Assembly Project path - https://github.com/imajaydwivedi/SQLMonitor/tree/master/TSQLTextNormalizer

The feature is accessible using clr function dbo.normalized_sql_text inside from with SQLServer post importing the assembly.

The function works as expected in case of normal stored procedure or statements. But, it does not recognize special syntax like sp_ExecuteSQL or sp_prepexec.

Would it be possible to include exceptional processing of sp_executesql or sp_prepexec like stored procedure?

Sample code to test -

select dbo.normalized_sql_text('exec sp_executesql ''select * from SomeTable01''',150,0)
select dbo.normalized_sql_text('exec sp_executesql ''select * from AnotherTable02''',150,0)

[arvindshmicrosoft]

@imajaydwivedi thanks for asking - just so that I am clear, is your objective:

1. Replace whatever is passed to these special procs, as a placeholder string like foo? For example, when encountering the statement exec sp_executesql 'select * from SomeTable01 where col1 = 999', output exec sp_executesql 'foo' as you indicated in your screenshot above?
2. OR do you want to tokenize all literals embedded inside the dynamic SQL string to some placeholder values? For example, when encountering the statement exec sp_executesql 'select * from SomeTable01 where col1 = 999' output exec sp_executesql 'select * from SomeTable01 where col1 = 0'