How can I remove all functions from the CLR assembly before uninstalling it in SQL Server?

Question

I get a 'drop assembly failed because it refers to an object error. As I understand it, I need to drop all the functions associated with this assembly before it crashes. Is there a universal way to do this?

Answer 1

You will need a little script:

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, deleting all assembly-dependent objects is unsafe, so be careful what is deleted.