Search code examples
sqlsql-server.net-assembly

SQL Server 2016 - Get sys.assembly_files by database name

I have a database that has a .Net CLR assembly added to it.

When I execute the below query by selecting that particular database, I get the assembly in the results.

SELECT * FROM sys.assembly_files

But when I execute the same above query by selecting any other database like master, I do not get the assembly in the results.

Is there any way to get the assembly in results by selecting the master database and passing the other database name in the query which has the assembly?

Solution

  • You use

    SELECT <cols> FROM database_name.sys.assembly_files;

    To do it dynamically (or in a loop, or whatever):

    DECLARE @dbname sysname,
        @exec nvarchar(1000);

SET @dbname = N'database_name';
SET @exec = QUOTENAME(@dbname) + N'.sys.sp_executesql';

EXEC @exec N'SELECT DB_NAME(), <cols> FROM sys.assembly_files;';

    Entered on a phone, so sorry if any typos.