sql-server - stored - sql clr example

SQL Server: cómo enumerar todas las funciones/procedimientos/objetos de CLR para el ensamblaje

Question

Pregunta: En SQL Server 2005, ¿cómo puedo hacer una lista de todas las funciones / procedimientos CLR de SQL que usan assembly xy (por ejemplo, MyFirstUdp)?

Por ejemplo, una función que enumera HelloWorld para el parámetro de consulta MyFirstUdp

CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].HelloWorld
GO

después de que corrí

CREATE ASSEMBLY MyFirstUdp FROM ''C:/Users/username/Documents/Visual Studio 2005/Projects/SQL_CLRdll/SQL_CLRdll/bin/Debug/SQL_CLRdll.dll

Puedo enumerar todos los ensamblados y todas las funciones / procedimientos, pero parece que no puedo asociar el ensamblaje a las funciones / procedimientos ...

Answer 1

Mira la vista sys.assembly_modules :

select * from sys.assembly_modules

Esto debería enumerar todas las funciones y los ensamblados en los que están definidos. Consulte la página de ayuda de Libros en línea al respecto.

Devuelve una fila para cada función, procedimiento o disparador definido por un ensamblado de Common Language Runtime (CLR).

Answer 2

Aquí se encuentra un script encontrado en 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

Además, tiene la opción de ver todos los lugares donde se utiliza ese objeto CLR .

Answer 3

Yo uso el siguiente 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]

Tenga en cuenta:

1. Los tipos definidos por el usuario (UDT) se encuentran en: sys.assembly_types

2. Solo puede enumerar las referencias CLR que se han utilizado en las instrucciones CREATE. No puede encontrar métodos CLR que aún no hayan sido referenciados por un CREATE. Es decir, no puedes decir: "dame una lista de métodos en este conjunto para el que puedo crear objetos SQL".

Answer 4

Aquí hay una generalización de la consulta de srutzky (arriba) que pasa por todos los DB en un servidor usando un cursor. Perdón por el formato, pero esto es útil si tienes que buscar a través de 500 DB que has heredado.

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

Answer 5

o puede usar SELECT * FROM sys.dm_clr_appdomains; que devuelve una lista de conjuntos y en qué base de datos se almacenan.

T