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.

Answer 2

Created this to check if the assembly exists and drop the aggregates as well (if exists):

DECLARE @assembly_name NVARCHAR(MAX) = 'AssemblyName'

DECLARE @sql VARCHAR(MAX) = ''
DECLARE @ObjectsToDrop AS TABLE (DropFunction NVARCHAR(500))
INSERT INTO @ObjectsToDrop
SELECT
    'DROP ' + CASE WHEN o.type = 'PC' THEN 'PROCEDURE ' WHEN o.type = 'AF' THEN 'AGGREGATE ' WHEN o.type IN ('FS','FT') THEN 'FUNCTION ' ELSE 'FUNCTION ' END + o.Name
FROM 
     sys.assemblies asm
     JOIN sys.assembly_modules m ON asm.assembly_id = m.assembly_id
     JOIN sys.objects o ON m.object_id = o.object_id
WHERE
    asm.name = @assembly_name

IF (SELECT COUNT(*) FROM @ObjectsToDrop) > 0
BEGIN
DECLARE db_cursor CURSOR FOR SELECT DropFunction FROM @ObjectsToDrop
    OPEN db_cursor 
    FETCH NEXT FROM db_cursor INTO @sql
    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC (@sql)
            PRINT @sql
            FETCH NEXT FROM db_cursor INTO @sql
        END
    CLOSE db_cursor
    DEALLOCATE db_cursor

    SET @sql = 'DROP ASSEMBLY ['+ @assembly_name + ']'
    PRINT @sql
    EXEC (@sql)
END
GO