如何查找在 SQL Server 中注册的程序集? [英] How to find the assembly registered in SQL Server?

问题描述

我在 SQL Server 中注册了一个程序集:

i have an assembly registered in SQL Server:

CREATE ASSEMBLY [CLRFunctions]
AUTHORIZATION [dbo]
FROM  0x4D5A90000300000...
WITH PERMISSION_SET = SAFE

如何找到这个程序集的路径?

How can i find the path to the this assembly?

推荐答案

不推荐使用的(从引入 SQLCLR 的 SQL Server 2005 发布开始)扩展存储过程 API/功能(即 XP)确实指向外部 DLL.这意味着它们可能会被删除并且不会包含在数据库的备份中.使用 SQLCLR，程序集被导入到数据库中，并且可以通过 sys.assembly_files 访问.将程序集包含在数据库中可确保它们包含在所有数据库备份中(以及不会在您不知情的情况下神奇地消失或被替换等其他好处).

The deprecated (as of the release of SQL Server 2005 which introduced SQLCLR) extended stored procedure API / feature (i.e. XPs) did point to external DLLs. This meant that they could get deleted and were not included in backups of the database. With SQLCLR, the assemblies are imported into the database and can be accessed via sys.assembly_files. Having the assemblies contained within the database guarantees that they are included in all DB backups (among the other benefits of not magically disappearing or being replaced without your knowledge, etc).

执行以下操作以查看您加载了哪些程序集、它们的原始路径(仅当它们从外部 DLL 加载而不是从 VARBINARY 文字/十六进制字节加载时)以及它们的完整内容/字节:

Execute the following to see what assemblies you have loaded, their original paths (only if they were loaded from an external DLL and not from a VARBINARY literal / hex bytes), and their full contents/bytes:

SELECT asm.[name] AS [Assembly], afl.[name] AS [PathOrAltName], afl.[content]
FROM   sys.assembly_files afl
INNER JOIN sys.assemblies asm
        ON asm.[assembly_id] = afl.[assembly_id]
ORDER BY asm.[name];

如果程序集是从 VARBINARY 文字/十六进制字节(即 0x4D5A9000...)加载的，则 [name] 列sys.assembly_files 中的 [name] 列应该与 sys.assemblies 中的列相同(这是 CREATE ASSEMBLY 语句).

If the assembly was loaded from a VARBINARY literal / hex bytes (i.e. 0x4D5A9000...), then the [name] column in sys.assembly_files should be the same as the [name] column in sys.assemblies (which is the name used in the CREATE ASSEMBLY statement).

有关使用 SQLCLR 的更多信息，请访问:SQLCLR 信息

For more information on working with SQLCLR in general, please visit: SQLCLR Info

这篇关于如何查找在 SQL Server 中注册的程序集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！