Get a List of CLR’s Registered to a Database

Get a list of CLR’s registered to a database.  This is helpful when migrating or troubleshooting.  For more information on CLR’s check out Books Online or http://msdn.microsoft.com/en-us/library/ms131089(SQL.100).aspx

SELECT o.name AS ObjectName, a.name AS Assembly,

o.type_desc AS ObjectType

FROM   sys.assembly_modules am

JOIN   sys.objects o ON am.object_id = o.object_id

JOIN   sys.assemblies a ON a.assembly_id = am.assembly_id

WHERE  a.is_user_defined = 1

UNION

SELECT at.name, a.name, ‘Type’

FROM   sys.assembly_types at

JOIN   sys.assemblies a ON at.assembly_id = a.assembly_id

WHERE  a.is_user_defined = 1