SQL Server：如何列出所有 CLR 函数过程对象以进行组装

使用SQL Server可以创建和使用CLR（公共语言运行时）函数、过程和对象。CLR是Microsoft的一种技术，它允许开发人员使用.NET语言（如C#或VB.NET）来编写数据库对象。在本文中，我们将学习如何列出所有CLR函数/过程/对象以进行组装。

sql
SELECT * 
FROM sys.assemblies AS a
JOIN sys.assembly_modules AS am ON a.assembly_id = am.assembly_id
JOIN sys.objects AS o ON am.object_id = o.object_id
WHERE o.type IN ('AF', 'PC', 'U')

csharp
using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class StringLength
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlInt32 GetLength(SqlString input)
    {
        if (input.IsNull)
            return SqlInt32.Null;
        return input.Value.Length;
    }
}

sql
CREATE ASSEMBLY StringLengthAssembly
FROM 'C:\Path\To\StringLength.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION dbo.StringLength(@input NVARCHAR(MAX))
RETURNS INT
AS EXTERNAL NAME StringLengthAssembly.StringLength.GetLength
GO

sql
SELECT o.name, o.type_desc, a.name AS assembly_name
FROM sys.assemblies AS a
JOIN sys.assembly_modules AS am ON a.assembly_id = am.assembly_id
JOIN sys.objects AS o ON am.object_id = o.object_id
WHERE o.type IN ('AF', 'PC', 'U')