SQL Server 2016 - Get sys.assembly_files by database name-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Depending on what you're trying to do, here's some powershell that will get the details for all files for all (non-system) assemblies in all user databases:

$s = Connect-DbaInstance -SqlInstance .;

foreach ($db in $s.Databases) {
   foreach ($a in $db.Assemblies | where IsSystemObject -eq $false) {
      $a.SqlAssemblyFiles;
   }
}

I'm using Connect-DbaInstance from dbatools to get an SMO Server object, but you're welcome to do that however you'd like.