Sql-server – View T-SQL in CLR Assembly

sql serversql-clr

I have a production database with numerous scalar functions. My goal is to create set based alternatives for reporting. Unfortunately, I don't have a good way to view the T-SQL actually being executed because the functions reference a CLR assembly. Is there a good way to view the T-SQL inside the assembly for each function?

Best Answer

T-SQL being executed is different than T-SQL that will be executed if the function is run.

  • To see what is currently executing, you would use either SQL Server Profiler, or Extended Events. Then you can execute a function and see what T-SQL it runs.

  • To see what the function might execute, that is a bit trickier. You do have the code in the sys.assembly_files system catalog view. The content field contains the binary representation of the assembly. But, to see the code you need to find a way to extract it to a binary file (no built-in method, and no, not a text file containing that "binary" string) and then decompile it.

    String literals should be contained as-is in the assembly, so theoretically you should be able to convert the VARBINARY value in sys.assembly_files.content to NVARCHAR, but there are two problems there:

    1. Queries can be constructed from several literals and/or variables, so it's hard to say if viewing this would be conclusive, and
    2. There are many 0x00 bytes, and those will stop the displaying of what comes after it because it is the "null" terminator for strings, so you need to find a way to convert those to spaces (while also not converting a trailing 0 of one byte followed by a leading 0 of the next byte).

So, perhaps just use SQL Server Profiler or Extended Events to see what T-SQL is submitted when the functions are executed.

Related Question