Get the T-SQL CREATE statement for SQLCLR stored procedures

Question

I have an application that retrieves the text of stored procedures using sp_helptext. It works great on all my stored procedures except for CLR stored procedures. If I try to use sp_helptext on a SQLCLR stored procedure, I get this error:

There is no text for object 'PROC_NAME'

I know SSMS can do it when I use the "script as" -> "create to" -> command.

But when I trace the SSMS "generate script" action with SQL Server Profiler, it gives me a dauntingly long list of fairly complex activity. I can slog through that if I must, but does anyone know a straightforward way to programmatically get the code of a CLR stored proc?

Edit for clarification
I don't want to see the actual code from the assembly; I'm just asking for an easy way to view the T-SQL code, as in this example:

CREATE PROCEDURE [dbo].[MY_PROC]
    @PARAM1 [xml],
    @PARAM2 [uniqueidentifier],
    @PARAM3 [nvarchar](255),
    @PARAM4[bit] = False
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME [SomeSolution.SomeProject].[SomeAssembly].[SomeMethod]
GO

In other words, the "SQL Server side" of the SQLCLR function.

Answer 1

I had the same dilemma and searched over and over on the web for any solution to get the code of a CLR stored procedure. Finally had to PROFILE what SSMS "generate script" action did as you said and here is what I got:

--GET ALL CLR stored procedures
SELECT
sp.name AS [Name],
sp.object_id AS [object_ID],
case when amsp.object_id is null then N'''' else asmblsp.name end AS [AssemblyName],
case when amsp.object_id is null then N'''' else amsp.assembly_class end AS [ClassName],
case when amsp.object_id is null then N'''' else amsp.assembly_method end AS [MethodName]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.assembly_modules AS amsp ON amsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assemblies AS asmblsp ON asmblsp.assembly_id = amsp.assembly_id
LEFT OUTER JOIN sys.procedures AS spp ON spp.object_id = sp.object_id
WHERE spp.type like 'PC'

--For each CLR SP get the parameters in use
SELECT
param.name AS [Name]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
WHERE sp.name like 'your_sp_name' order by param.parameter_id ASC

--For each parameter get the values, data type and so on...
SELECT
param.name AS [Name],
param.parameter_id AS [param_ID],
sp.object_id AS [object_ID],
param.default_value AS [DefaultValue],
usrt.name AS [DataType],
sparam.name AS [DataTypeSchema],
ISNULL(baset.name, N'''') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN         param.max_length/2 ELSE param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
LEFT OUTER JOIN sys.schemas AS sparam ON sparam.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and     baset.user_type_id = baset.system_type_id) 
WHERE param.name='@param1' and sp.name='your_sp_name'

With this scripts I made a Perl script to generate the code for me. I guess from here you could do the same or create your own stored procedure to print the desired code. I'm not a SQL programmer so I don't know how to do that, but if someone does the programming of the above queries, please share it.

Answer 2

A CLR stored procedure will not have text as such as it is a link to a DLL. You will need to get the source code to the DLL

See Common Language Runtime (CLR) Integration Programming Concepts for example

Answer 3

Short answer: no, you can't do that. (At least not programatically/easily) CLR procedures are loaded from .NET assemblies (binary files) and there is no simple way to get the source code of such file. Not within SQL Server. But you can use tools like RedGate Reflector to disassemble DLL and view/recover source code of the stored procedure.