How to list CLR objects inside a SQL Server database

Troubleshooting an error message An error occurred in the Microsoft .NET Framework while trying to load assembly id xxx and needed to identify  CLRs related to a particular database.

This query lists the CLR objects inside a SQL Server database. Use the information in this query to start diagnosing the error messages.

use myDB
go
SELECT o.object_id AS [object_ID]
      , a.assembly_id AS [assemblyID]
	 ,a.name AS [assemblyName]
    ,schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName]
    ,o.type_desc AS [CLRType]
    ,o.create_date AS [DateCreated]
    ,o.modify_date AS [SateModified]
    ,a.permission_set_desc AS [CLRPermission]
	
FROM sys.objects o
INNER JOIN sys.module_assembly_usages ma
    ON o.object_id = ma.object_id
INNER JOIN sys.assemblies a
    ON ma.assembly_id = a.assembly_id

There are different reasons for CLR error messages.Here are some  links to posts which have more queries and troubleshooting methods for CLR related problems

Troubleshooting SQL CLR memory pressure

How to list all CLR objects and associated procedures

How to get the DNS API permissions in CLR assembly

A quick note on setting the database setting TRUSTWORTHY to ON. By default databases are set as TRUSTWORTHY is OFF. A common solution you'll see on forums and other blog posts is to set the option to ON, that way if you have an assembly with a permission set of UNSAFE and the owner of the database has elevated authority - such as sysadmin then it is possible to create assemblies with a permission of UNSADE and execute the assemblies.

DON'T just follow this advice blindly. As a guide manage trust on a application specific basis .