Search code examples
sql-serversql-server-2008-r2sqlclr

How to find the assembly registered in 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?

Solution

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

    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];

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

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