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