Using user defined assemblies from Powershell

Question

I am trying to extract list of user defined assemblies through PowerShell for one of SQL Server administration for automation. When I open SSMS and execute this query as

Select name, permission_set_desc 
From sys.assemblies

I am able to get the output as 2 rows as below

Name	Permission_Set_Desc
Microsoft.SqlServer.Types	UNSAFE_ACCESS
StairwayToSQLCLR-02-Example	SAFE_ACCESS

When I execute the same T-SQL through PowerShell using Invoke-SQLCMD command, I do not get any user defined assemblies rather only system defined assembly.

This is the command I used in PowerShell:

$query = "Select name, permission_Set_Desc from sys.assemblies"
Invoke-Sqlcmd -Query $query -AbortOnError -OutputSQLErrors $true

I get the below only which is system defined assembly.

Name	Permission_Set_Desc
Microsoft.SqlServer.Types	UNSAFE_ACCESS

I am unable to get the user defined assemblies from PowerShell.

Did I miss something here?

Answer 1

As @Vijayanand A said, assemblies are defined at database level. However, you can iterate all databases and display all of them with attached assemblies:

DECLARE @sql nvarchar(MAX) = '';

SELECT @sql += 'SELECT '+
    QUOTENAME(DB.Name, '''') + ' COLLATE database_default db, '+
    'name COLLATE database_default name, '+
    'permission_set_desc COLLATE database_default permission_set_desc '+
  'FROM ' + QUOTENAME(DB.Name) + '.sys.assemblies UNION ALL '
FROM sys.databases DB

SET @sql = LEFT(@sql, LEN(@sql)-LEN(' UNION ALL'))

EXEC(@sql)