List all assemblies in a database

Understanding Assembly Modules in SQL Server

The assembly_modules catalog view in SQL Server returns a row for each function, procedure, or trigger defined by a Common Language Runtime (CLR) assembly. This view helps to map CLR stored procedures, CLR triggers, and CLR functions to their underlying implementations, providing insight into how CLR objects are defined and utilized in the database.

Here is a SQL query that can be used to retrieve detailed information about assembly modules:

SELECT   so.[Name], so.[type], SCHEMA_NAME(so.SCHEMA_ID) AS [Schema],
         asmbly.[Name], asmbly.permission_set_desc, am.assembly_class, 
         am.assembly_method
FROM [sys].assembly_modules am
   INNER JOIN  [sys].assemblies asmbly
   ON  asmbly.assembly_id = am.assembly_id AND asmbly.[Name] NOT LIKE 'Microsoft%'
   INNER JOIN  [sys].objects so
   ON  so.OBJECT_ID = am.OBJECT_ID
UNION
SELECT   at.[Name], 'TYPE' AS [type], SCHEMA_NAME(AT.SCHEMA_ID) AS [Schema], 
         asmbly.[Name], asmbly.permission_set_desc, AT.assembly_class,
         NULL AS [assembly_method]
FROM [sys].assembly_types at
   INNER JOIN  [sys].assemblies asmbly
   ON  asmbly.assembly_id = at.assembly_id
   AND asmbly.[Name] NOT LIKE 'Microsoft%'
ORDER BY 4, 2, 1;

This query joins several system views (assembly_modules, assemblies, objects, and assembly_types) to pull a comprehensive set of information, including the assembly name, schema, class, and method. It excludes assemblies from Microsoft for better relevance in custom scenarios.

For more detailed information about CLR assemblies in SQL Server, refer to the official documentation from Microsoft: Assemblies (Database Engine) - SQL Server | Microsoft Docs.