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

Question

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

For example a function that lists HelloWorld for query parameter MyFirstUdp

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...

Answer 1

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

Answer 2

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.