SQL Server: How to list all CLR functions/procedures/objects for assembly

Check out the sys.assembly_modules view:

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.


Here it a script found on 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.


I use the following 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:

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

  2. You can only list SQLCLR references that have been used in CREATE { PROCEDURE | FUNCTION | AGGREGATE | TRIGGER | TYPE } statements. You cannot find SQLCLR 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 T-SQL objects for".

For more info on working with SQLCLR in general, please visit: SQLCLR Info