How do I drop all functions from CLR Assembly before it's drop in SQL Server?

Question

I am getting 'drop assembly failed because it is referenced by object' error. As I understand I need to drop all functions referenced to this assembly before it's drop. Is there any universal way how to do that?

Answer 1

You'll need a little script like that:

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @assembly_name NVARCHAR(MAX)='assembly'
SELECT @sql += '
DROP ' + 
CASE
 WHEN o.type='PC' THEN 'PROCEDURE ' 
 ELSE 'FUNCTION '
END
+ QUOTENAME(o.Name)
+ ';'
FROM Sys.Assemblies asm
INNER JOIN SYS.ASSEMBLY_MODULES m ON asm.assembly_id=m.assembly_id
INNER JOIN SYS.OBJECTS o ON m.object_id = o.object_id
WHERE asm.name=@assembly_name
SET @sql=@sql+'
DROP ASSEMBLY '+QUOTENAME(@assembly_name)

PRINT @sql;
EXEC sp_executesql @sql;

However, dropping all assembly's dependent objects is not safe, so take care what will be deleted.