SQL SERVER – DROP ASSEMBLY failed because '%ls' is referenced by object '%ls'

In this article we will discuss about an error message (DROP ASSEMBLY failed because '%ls' is referenced by object '%ls') related to Assembly.

Msg 6590, Level 16, State 1, Procedure procedure_name, Line 25
DROP ASSEMBLY failed because 'testi' is referenced by object 'test_wi'

when you are trying to drop assembly but it's dependent object exists in database you will get error:

Drop assembly assembly_name;

Msg 6590, Level 16, State 1, Line 1
DROP ASSEMBLY failed because 'testi' is referenced by object 'test_wi'.

Solution:
With this query you can find dependent objects

SELECT A.assembly_id,A.name as [Assembly Name],
B.object_id, C. name as [Object Name], C.type, C.type_desc
FROM Sys.Assemblies A
INNER JOIN SYS.ASSEMBLY_MODULES B oN a.assembly_id=B.assembly_id
INNER JOIN SYS.OBJECTS C ON B.object_id = C.object_id

in the above result set, you have the assembly name along with its dependent's objects names (CLR functions).
So you can drop CLR functions and then drop Assembly.

You cannot drop assembly without dropping its object. Preferably, you should alter the assembly not to drop. But if you need to drop, first drop all its objects and then you can drop Assembly.