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:
User-Defined Types (UDTs) are found in:
sys.assembly_types
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 aCREATE
. 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