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.