SQL Server 2016 – Get sys.assembly_files by database name

March 22, 2022

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.