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

Unroof

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.

Admittance

Just FYI: User-Defined Types (UDTs) are found in sys.assembly_types and not in sys.assembly_modules. – Rambort

Answer 2

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

Rambort

Answer 3

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

Tootsy

Perfect. Just what I needed. – Libelee

Answer 4

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.

Martijn

Answer 5

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

Spritsail

First answer that includes the path to the actual assembly on disk! – Sidwel

Answer 6

Or you can use

SELECT * FROM sys.dm_clr_appdomains;

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

Selfdelusion

Answer 7

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

Nicolas