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

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

Here is a generalization of srutzky's query (above) that goes through all DBs on a server using a cursor. Sorry about the formatting, but this is handy if you have to search through 500 DB's you've inherited.

set nocount on declare @cmd nvarchar(4000) declare curDBs cursor read_only for SELECT name FROM MASTER.sys.sysdatabases declare @NameDB nvarchar(100) create table #tmpResults ( DatabaseName nvarchar(128) , ObjectName nvarchar(128) , ObjectType char(2) , SchemaName nvarchar(128) , AssemblyName nvarchar(128) , PermissionSet nvarchar(60) , AssemblyClass nvarchar(128) , AssemblyMethod nvarchar(128)); open curDBs; while (1=1) begin fetch next from curDBs into @NameDB if @@fetch_status <> 0 break set @cmd = N' USE [' + @NameDB + N']; begin try insert into #tmpResults SELECT ''' + @NameDB + N''', so.name AS [ObjectName], so.[type], SCHEMA_NAME(so.[schema_id]) AS [SchemaName], asy.name AS [AssemblyName], asy.permission_set_desc, am.assembly_class, am.assembly_method FROM sys.assembly_modules am INNER JOIN sys.assemblies asy ON asy.assembly_id = am.assembly_id AND asy.is_user_defined = 1 INNER JOIN sys.objects so ON so.[object_id] = am.[object_id] UNION ALL SELECT ''' + @NameDB + N''', at.name AS [ObjectName], ''UDT'' AS [type], SCHEMA_NAME(at.[schema_id]) AS [SchemaName], asy.name AS [AssemblyName], asy.permission_set_desc, at.assembly_class, NULL AS [assembly_method] FROM sys.assembly_types at INNER JOIN sys.assemblies asy ON asy.assembly_id = at.assembly_id AND asy.is_user_defined = 1 ORDER BY [AssemblyName], [type], [ObjectName] print ''' + @NameDB + N' ' + cast(@@rowcount as nvarchar) + N''' end try begin catch print ''Error processing ' + @NameDB + ''' end catch ' --print @cmd EXEC sp_executesql @cmd end close curDBs; deallocate curDBs select * from #tmpResults drop table #tmpResults 

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.

SELECT modules.assembly_class AS AssemblyClass, modules.assembly_method AS MethodName, obj.type_desc AS MethodType, files.name AS FilePath, assemb.name AS AssemblyName, assemb.clr_name, assemb.create_date, assemb.modify_date, assemb.permission_set_desc --,* FROM sys.assembly_modules AS modules JOIN sys.assembly_files AS files ON files.assembly_id = modules.assembly_id JOIN sys.assemblies AS assemb ON assemb.assembly_id = modules.assembly_id JOIN sys.objects AS obj ON obj.object_id = modules.object_id 

Or you can use

SELECT * FROM sys.dm_clr_appdomains; 

which returns a list of assemblies and in what database they are stored.

all answers offer queries returning list of methods BEING USED in user-defined db objects. but as i understand question was about all POSSIBLE methods inside assembly which can be used in future. unfortunately for now developer must find such list from external description of imported clr-assembly