sql-server - 获取 SQLCLR 存储过程的 T-SQL CREATE 语句

Question

我有一个使用 sp_helptext 检索存储过程文本的应用程序.除了 CLR 存储过程之外，它对我所有的存储过程都非常有效。如果我尝试使用 sp_helptext在 SQLCLR 存储过程上，我收到此错误:

There is no text for object 'PROC_NAME'

我知道当我使用“脚本为”->“创建到”-> 命令时，SSMS 可以做到。

但是，当我使用 SQL Server Profiler 跟踪 SSMS“生成脚本”操作时，它给了我一长串相当复杂的事件。如果必须的话，我可以通过它，但是有没有人知道以编程方式获取 CLR 存储过程的代码的直接方法？

编辑澄清
我不想看到程序集中的实际代码；我只是想要一种简单的方法来查看 T-SQL 代码，如本例所示:

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

换言之，SQLCLR 函数的“SQL Server 端”。

Answer 1

我遇到了同样的困境，并在网上一遍又一遍地搜索任何解决方案来获取 CLR 存储过程的代码。最后不得不对 SSMS“生成脚本”操作做了什么，正如你所说，这是我得到的:

--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'

使用这个脚本，我制作了一个 Perl 脚本来为我生成代码。我想从这里你可以做同样的事情或创建自己的存储过程来打印所需的代码。我不是 SQL 程序员，所以我不知道该怎么做，但是如果有人对上述查询进行了编程，请分享。