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

sql-server sql-server-2005 sqlclr
Stefan Steiger picture Stefan Steiger · Jul 1, 2010 · Viewed 50.6k times · Source

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

marc_s picture marc_s · Jul 1, 2010

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.

Related questions

How to call C# function in stored procedure

SQL Server 2005 supports CLR so it means we can use CLR in backend so how to do that, I have some function in c# which do some complex manipulation with date-time variable now I want to use those functions in …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More