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

Question

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

对象“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 程序员，所以我不知道该怎么做，但如果有人对上述查询进行编程，请分享。

Answer 2

CLR 存储过程不会包含文本，因为它是 DLL 的链接。您需要获取 DLL 的源代码，

请参阅公共语言运行时 (CLR) 集成例如，编程概念

Answer 3

简短的回答：不，你不能这样做。 （至少不是以编程方式/轻松地）
CLR 过程是从 .NET 程序集（二进制文件）加载的，并且没有简单的方法来获取此类文件的源代码。不在 SQL Server 内。
但是您可以使用 RedGate Reflector 等工具来反汇编 DLL 并查看/恢复存储过程的源代码。

Answer 4

1. 与类似的 T-SQL 对象不同，CREATE [ STORED PROCEDURE |功能|触发|类型 | SQLCLR 对象的 AGGREGATE ] ... 语句不会按原样存储；它们是通过对象类型以及 sys.assemblies、sys. assembly_modules、sys.parameters、sys.types 的组合派生的 和 sys. assembly_types。

   如果您尝试使用 T-SQL 或其他非 .NET 语言构建这些 CREATE 语句，那么您需要从这些表中选择适当的数据并将其组合在一起。但是，如果您使用 .NET，则可以使用 SMO 库中的脚本编写器类 (Microsoft.SqlServer.Smo.dll)。它应该能够在 SQL Server 中编写任何内容的脚本。事实上，您可能应该使用它为您的工具编写所有内容的脚本，而不是使用查询。

2. 除了在 SSMS 中动态查看存储过程/函数/触发器定义（即快速且简单）之外，您不应该使用 sp_helptext 来提取对象定义。它做了很多不必要的工作，因为自 SQL Server 2000 以来它似乎只进行了少量更新。它的两个主要问题是：

   1. 它不处理 > 的单独行。 4000 个字符长
   2. 它只允许 CRLF（即 \r\n ）换行符，而不是有效的仅 LF 换行符（即 \n ）。

   您可以通过查看该系统存储过程的定义来了解这一点：

   EXEC sp_helptext 'sp_helptext';
   

   相反，您应该使用 OBJECT_DEFINITION() 内置函数（它采用 object_id 作为输入）或从包含以下内容的系统目录视图之一进行选择：这些定义：sys.sql_modules、sys.server_sql_modules、sys.system_sql_modules 和 sys.system_sql_modules。 p>

Answer 5

我们遇到了更改未添加到源代码管理中的问题，因此我执行了以下操作来获取更新。

1. 使用 sqlservermanager 我查询了所有 sys. assembly_files
   找到我需要的东西。

2. 编写了一个快速控制台程序来获取已更改文件的源。

   • 在我们的例子中，它适用于 assembly_id 为 68541 的记录以及 file_id 均大于 3 的多个文件

   • cs 文件的源位于名为 content 的列中，文件名位于名为 name 的列中。我将内容转换为字节数组，然后转换为字符串，并使用 filename.txt 将内容写入文本文件。

   • 复制并粘贴更新的代码并推送到源代码管理。

     using (SqlConnection conn = new SqlConnection(connstring))
     {
         使用 (SqlCommand cmd = new SqlCommand("SELECT * FROM sys.assemble_files WHERE assembly_id = 68541 and file_id > 3", conn))
         {
             DataTable ds = new DataTable();
             使用 (SqlDataAdapter da = new SqlDataAdapter(cmd))
             {
                 da.Fill(ds);
                 foreach（ds.Rows 中的 DataRow r）
                 {
                     byte[] binaryString = (byte[])r["内容"];
                     字符串 x = Encoding.UTF8.GetString(binaryString);
                     字符串文件名 = @"C:\SQLCLR";
                     string filePath = string.Format(@"{0}\{1}.txt", filename,r["name"]);
                     File.WriteAllText(文件路径, x);
     
                 }
             }
         }
     }
     

Answer 6

实际上，您可以通过检查 sys. assembly_files 目录视图来做到这一点：

SELECT CONVERT(VARCHAR(MAX), content) as my_source_code 
FROM sys.assembly_files

Answer 7

要提取程序集，请运行以下命令：

DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT

SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'D:\SqlServerProject1.dll', 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

然后使用 Redgate Reflector 或任何 .Net 反编译器（ilspy，...）对其进行反编译

Answer 8

这个问题很老了，但是互联网上没有其他来源，只是说明如何使用 SSMS 脚本。

作为 Alex Castillo，我还对 SSMS 脚本过程进行了分析和逆向工程，然后将其全部合并到一个 T-SQL 查询中以方便使用。这是我得到的：

SELECT 
    'DROP ' +
    CASE o.[type]
        WHEN 'FT' THEN 'FUNCTION'
        WHEN 'FS' THEN 'FUNCTION'
        WHEN 'PC' THEN 'PROCEDURE'
    END + ' [' + SCHEMA_NAME(ao.[schema_id]) + '].[' + o.[name] + '];' AS [drop_statement]
    ,'CREATE ' +
    CASE o.[type]
        WHEN 'FT' THEN 'FUNCTION'
        WHEN 'FS' THEN 'FUNCTION'
        WHEN 'PC' THEN 'PROCEDURE'
    END + ' [' + SCHEMA_NAME(ao.[schema_id]) + '].[' + o.[name] + ']  ' +
    CASE o.[type]
        WHEN 'FT' THEN '(' + COALESCE(inp.[params_def], '') + ') RETURNS TABLE (' + oc.[columns_def] + ')'
        WHEN 'FS' THEN '(' + COALESCE(inp.[params_def], '') + ') RETURNS ' + t.[name] + CASE WHEN t.[name] IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar', 'datetime2') THEN '(' + CASE WHEN t.[name] IN ('nchar', 'nvarchar') AND aop.[max_length] != -1 THEN CAST(aop.[max_length] / 2 AS [varchar]) WHEN t.[name] IN ('nvarchar') AND aop.[max_length] = -1 THEN 'MAX' WHEN t.[name] = 'datetime2' THEN CAST(aop.[scale] AS [varchar]) ELSE CAST(aop.[max_length] AS [varchar]) END  + ')' WHEN t.[name] IN ('numeric', 'decimal') THEN '(' + CAST(aop.[precision] AS [varchar]) + ', ' + CAST(aop.[scale] AS [varchar]) + ')' ELSE '' END
        WHEN 'PC' THEN COALESCE(inp.[params_def], '')
    END + ' WITH EXECUTE AS CALLER AS ' +
    ' EXTERNAL NAME [' + a.[name] COLLATE SQL_Latin1_General_CP1_CI_AS + '].[' + am.[assembly_class] + '].[' + am.[assembly_method] + '];' AS [create_statement]
FROM sys.assemblies a WITH(NOLOCK)
JOIN sys.assembly_modules am WITH(NOLOCK) ON a.[assembly_id] = am.[assembly_id]
JOIN sys.objects o WITH(NOLOCK) ON am.[object_id] = o.[object_id]
LEFT JOIN sys.all_parameters AS aop WITH(NOLOCK) ON aop.[object_id] = am.[object_id] AND aop.[is_output] = 1
LEFT JOIN sys.types AS t WITH(NOLOCK) ON (t.[user_type_id] = aop.[system_type_id] AND t.[user_type_id] = t.[system_type_id]) OR ((t.[system_type_id] = aop.[system_type_id]) AND (t.[user_type_id] = aop.[user_type_id]) AND (t.[is_user_defined] = 0) AND (t.[is_assembly_type] = 1)) 
JOIN sys.all_objects ao WITH(NOLOCK) ON o.[object_id] = ao.[object_id]
OUTER APPLY (
    SELECT
        SUBSTRING((SELECT
        ', ' + aip.[name] + ' ' + it.[name] + CASE WHEN it.[name] IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar', 'datetime2') THEN '(' + CASE WHEN it.[name] IN ('nchar', 'nvarchar') AND aip.[max_length] != -1 THEN CAST(aip.[max_length] / 2 AS [varchar]) WHEN it.[name] IN ('nvarchar') AND aip.[max_length] = -1 THEN 'MAX' WHEN it.[name] = 'datetime2' THEN CAST(aip.[scale] AS [varchar]) ELSE CAST(aip.[max_length] AS [varchar]) END  + ')' WHEN it.[name] IN ('numeric', 'decimal') THEN '(' + CAST(aip.[precision] AS [varchar]) + ', ' + CAST(aip.[scale] AS [varchar]) + ')' ELSE '' END AS [params_def]
    FROM sys.all_parameters AS aip WITH(NOLOCK)
    LEFT JOIN sys.types AS it WITH(NOLOCK) ON (it.[user_type_id] = aip.[system_type_id] AND it.[user_type_id] = it.[system_type_id]) OR ((it.[system_type_id] = aip.[system_type_id]) AND (it.[user_type_id] = aip.[user_type_id]) AND (it.[is_user_defined] = 0) AND (it.[is_assembly_type] = 1)) 
    WHERE 1 = 1
        AND aip.[is_output] = 0
        AND aip.[object_id] = am.[object_id]
    ORDER BY aip.[parameter_id]
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(MAX)'), 3, 2147483647) AS [params_def]
) AS inp
OUTER APPLY (
    SELECT 
        SUBSTRING((SELECT
            ', [' + ac.[name] + '] ' + ct.[name] + CASE WHEN ct.[name] IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar', 'datetime2') THEN '(' + CASE WHEN ct.[name] IN ('nchar', 'nvarchar') AND ac.[max_length] != -1 THEN CAST(ac.[max_length] / 2 AS [varchar]) WHEN ct.[name] IN ('nvarchar') AND ac.[max_length] = -1 THEN 'MAX' WHEN ct.[name] = 'datetime2' THEN CAST(ac.[scale] AS [varchar]) ELSE CAST(ac.[max_length] AS [varchar]) END  + ')' WHEN ct.[name] IN ('numeric', 'decimal') THEN '(' + CAST(ac.[precision] AS [varchar]) + ', ' + CAST(ac.[scale] AS [varchar]) + ')' ELSE '' END + CASE ac.[is_nullable] WHEN 0 THEN ' NOT' ELSE '' END + ' NULL'
        FROM sys.all_columns ac
        LEFT JOIN sys.types AS ct WITH(NOLOCK) ON (ct.[user_type_id] = ac.[system_type_id] AND ct.[user_type_id] = ct.[system_type_id]) OR ((ct.[system_type_id] = ac.[system_type_id]) AND (ct.[user_type_id] = ac.[user_type_id]) AND (ct.[is_user_defined] = 0) AND (ct.[is_assembly_type] = 1)) 
        WHERE 1 = 1
            AND ac.[object_id] = am.[object_id]
        ORDER BY ac.[column_id]
        FOR XML PATH, TYPE).value('.[1]', 'nvarchar(MAX)'), 3, 2147483647) AS [columns_def]
) AS oc
WHERE 1 = 1
    AND a.[name] = <'your assembly name'>
    AND SCHEMA_NAME(ao.[schema_id]) = <'your schema name'>

只需执行查询并将相应输出行中的值复制粘贴到新的查询窗口中并执行。该查询涵盖了很多方面，但不包括用户定义的类型（如果 SQL CLR 例程中有任何类型）。调整起来并不困难，只是我的代码中没有使用 SQL CLR UDT。
我希望有人会觉得它有用。

Answer 9

这非常简单 - 如果您可以访问 SQL Server Management Studio。

右键单击 CLR 存储过程，然后选择 CREATE SCRIPT - 瞧。

快乐的 CLR。

-丹麦语。