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

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.