SQL Server:如何列出所有CLR函数/过程/对象以进行汇编

问题:在SQL Server 2005中,如何列出使用程序集xy的所有SQL CLR函数/过程(例如MyFirstUdp)？

例如,为查询参数MyFirstUdp列出HelloWorld的函数

CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].HelloWorld
GO

我跑了之后

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Users\username\Documents\Visual Studio 2005\Projects\SQL_CLRdll\SQL_CLRdll\bin\Debug\SQL_CLRdll.dll

我可以列出所有程序集和所有函数/过程,但我似乎无法将程序集与函数/过程相关联...

查看sys.assembly_modules视图:

select * from sys.assembly_modules

这应列出所有功能及其定义的程序集.请参阅联机丛书帮助页面.

为公共语言运行库(CLR)程序集定义的每个函数,过程或触发器返回一行.

我使用以下SQL:

SELECT      so.name AS [ObjectName],
            so.[type],
            SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
            asmbly.name AS [AssemblyName],
            asmbly.permission_set_desc,
            am.assembly_class, 
            am.assembly_method
FROM        sys.assembly_modules am
INNER JOIN  sys.assemblies asmbly
        ON  asmbly.assembly_id = am.assembly_id
        AND asmbly.is_user_defined = 1 -- if using SQL Server 2008 or newer
--      AND asmbly.name NOT LIKE 'Microsoft%' -- if using SQL Server 2005
INNER JOIN  sys.objects so
        ON  so.[object_id] = am.[object_id]
UNION ALL
SELECT      at.name AS [ObjectName],
            'UDT' AS [type],
            SCHEMA_NAME(at.[schema_id]) AS [SchemaName], 
            asmbly.name AS [AssemblyName],
            asmbly.permission_set_desc,
            at.assembly_class,
            NULL AS [assembly_method]
FROM        sys.assembly_types at
INNER JOIN  sys.assemblies asmbly
        ON  asmbly.assembly_id = at.assembly_id
        AND asmbly.is_user_defined = 1 -- if using SQL Server 2008 or newer
--      AND asmbly.name NOT LIKE 'Microsoft%' -- if using SQL Server 2005
ORDER BY    [AssemblyName], [type], [ObjectName]