SQL Server:如何列出组装的所有CLR函数/过程/对象

Question: In SQL Server 2005, how can I list all SQL CLR-functions/procedures that use assembly xy (e.g. MyFirstUdp) ?

问:在SQL Server 2005中，我如何列出使用程序集xy(例如MyFirstUdp)的所有SQL clr函数/程序?

For example a function that lists HelloWorld for query parameter MyFirstUdp

例如，一个为查询参数MyFirstUdp列出HelloWorld的函数

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

after I ran

之后我跑

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

I can list all assemblies and all functions/procedures, but I seem to be unable to associate the assembly to the functions/procedures...

我可以列出所有程序集和所有函数/过程，但是我似乎无法将程序集与函数/过程联系起来……

5 个解决方案

#1

Check out the sys.assembly_modules view:

查看系统。assembly_modules视图:

select * from sys.assembly_modules

This should list all functions and the assemblies they're defined in. See the Books Online help page about it.

它应该列出所有函数及其定义的程序集。请参阅在线帮助页面。

Returns one row for each function, procedure or trigger that is defined by a common language runtime (CLR) assembly.

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

#2

I use the following SQL:

我使用以下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]

Please note:

请注意:

User-Defined Types (UDTs) are found in: sys.assembly_types

用户定义类型(udf)可以在:sys.assembly y_types中找到
You can only list CLR references that have been used in CREATE statements. You cannot find CLR methods that have not yet been referenced by a CREATE. Meaning, you cannot say: "give me a list of methods in this assembly that I can create SQL objects for".

您只能列出在CREATE语句中使用的CLR引用。您无法找到尚未被CREATE引用的CLR方法。也就是说，您不能说:“在这个程序集中为我创建SQL对象的方法列表”。

#3

Here it a script found on sqlhint.com:

这里是sqlhint.com上的脚本:

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class, 
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

Also, you have the option to see all the places where that CLR object is used.

此外，您还可以选择查看使用CLR对象的所有位置。

#4

Here is a generalization of srutzky's query (above) that goes through all DBs on a server using a cursor. Sorry about the formatting, but this is handy if you have to search through 500 DB's you've inherited.

下面是对srutzky查询(上面的查询)的概括，该查询使用游标遍历服务器上的所有DBs。不好意思格式化了，但是如果你要搜索你继承的500db的话，这很方便。

set nocount on
declare @cmd nvarchar(4000)

declare curDBs cursor read_only for 
    SELECT name FROM MASTER.sys.sysdatabases
declare @NameDB nvarchar(100)

create table #tmpResults (
      DatabaseName nvarchar(128)
    , ObjectName nvarchar(128)
    , ObjectType char(2)
    , SchemaName nvarchar(128)
    , AssemblyName nvarchar(128)
    , PermissionSet nvarchar(60)                        
    , AssemblyClass nvarchar(128)
    , AssemblyMethod nvarchar(128));

open curDBs; while (1=1)
    begin
        fetch next from curDBs into @NameDB
        if @@fetch_status <> 0 break
        set @cmd = N'
            USE [' + @NameDB + N'];
            begin try
            insert into #tmpResults
                SELECT      ''' + @NameDB + N''',
                            so.name AS [ObjectName],
                            so.[type],
                            SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
                            asy.name AS [AssemblyName],
                            asy.permission_set_desc,
                            am.assembly_class,  
                            am.assembly_method  
                    FROM sys.assembly_modules am
                        INNER JOIN  sys.assemblies asy
                            ON  asy.assembly_id = am.assembly_id
                                AND asy.is_user_defined = 1 
                        INNER JOIN  sys.objects so
                            ON  so.[object_id] = am.[object_id]
            UNION ALL
                SELECT      ''' + @NameDB + N''',
                            at.name AS [ObjectName],
                            ''UDT'' AS [type],
                            SCHEMA_NAME(at.[schema_id]) AS [SchemaName], 
                            asy.name AS [AssemblyName],
                            asy.permission_set_desc,
                            at.assembly_class,
                            NULL AS [assembly_method]
                    FROM  sys.assembly_types at
                        INNER JOIN  sys.assemblies asy
                            ON  asy.assembly_id = at.assembly_id
                                AND asy.is_user_defined = 1 
                    ORDER BY    [AssemblyName], [type], [ObjectName]
                print ''' + @NameDB + N'  ' +  cast(@@rowcount as nvarchar) + N'''
            end try
            begin catch
                print ''Error processing ' + @NameDB  + '''
            end catch
        '
        --print @cmd
        EXEC sp_executesql @cmd
    end
close curDBs; deallocate curDBs

select * from #tmpResults
drop table #tmpResults

#5

or you can use SELECT * FROM sys.dm_clr_appdomains; which returns a list of assemblies and in what database they are stored.

或者可以使用sys.dm_clr_appdomain中的SELECT *;返回程序集的列表以及它们存储在哪个数据库中。

#1