SQL Server T-SQL script to drop-and-create a stored procedure to drop a database CLR assembly and all of its dependents, including other assemblies

dbo.DropSqlServerAssemblyAndDependents.sql

IF EXISTS ( SELECT *
            FROM sys.objects
            WHERE   object_id = OBJECT_ID(N'dbo.DropSqlServerAssemblyAndDependents')
                    AND type IN ( N'P', N'PC' )                                      )

    DROP PROCEDURE dbo.DropSqlServerAssemblyAndDependents;
GO

CREATE PROCEDURE dbo.DropSqlServerAssemblyAndDependents
    @AssemblyName sysname
AS



        -- Drop procedures and functions that reference this assembly

    DECLARE assemblyProcedureFunctionReferencesCursor CURSOR LOCAL
        FORWARD_ONLY
        STATIC
        READ_ONLY
        FOR     SELECT SchemaName = s.[name], ObjectName = o.[name], ObjectType = o.[type]
                FROM    sys.assembly_modules am
                        JOIN sys.assemblies a ON am.assembly_id = a.assembly_id
                        JOIN sys.objects o ON am.[object_id] = o.[object_id]
                        JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
                WHERE a.[name] = @AssemblyName;

    OPEN assemblyProcedureFunctionReferencesCursor;

    DECLARE @schemaName sysname,
            @objectName sysname,
            @objectType char(2);

    FETCH NEXT FROM assemblyProcedureFunctionReferencesCursor
    INTO @schemaName, @objectName, @objectType;

    DECLARE @statement nvarchar(MAX);

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @statement =      N'DROP'
                            + N' '
                            +   CASE
                                    WHEN @objectType = 'PC'            THEN N'PROCEDURE'
                                    WHEN @objectType IN ( 'FS', 'FT' ) THEN N'FUNCTION'
                                END
                            + N' '
                            + N'[' + @schemaName + N']'
                            + N'.'
                            + N'[' + @objectName + N']'
                            + N';'
                            ;


        EXEC sp_executesql @statement = @statement;


        FETCH NEXT FROM assemblyProcedureFunctionReferencesCursor
        INTO @schemaName, @objectName, @objectType;

    END



        -- Drop assemblies that reference this one

    DECLARE assemblyAssemblyReferencesCursor CURSOR LOCAL
        FORWARD_ONLY
        STATIC
        READ_ONLY
        FOR     SELECT AssemblyName = a.[name]
                FROM    sys.assembly_references ar
                        JOIN sys.assemblies a ON ar.assembly_id = a.assembly_id
                        JOIN sys.assemblies a_referenced ON ar.referenced_assembly_id = a_referenced.assembly_id
                WHERE a_referenced.[name] = @AssemblyName;

    OPEN assemblyAssemblyReferencesCursor;

    DECLARE @referencingAssemblyName sysname;

    FETCH NEXT FROM assemblyAssemblyReferencesCursor
    INTO @referencingAssemblyName;


    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF EXISTS ( SELECT * FROM sys.assemblies WHERE [name] = @referencingAssemblyName )

            EXEC dbo.DropSqlServerAssemblyAndDependents
                @AssemblyName = @referencingAssemblyName;


        FETCH NEXT FROM assemblyAssemblyReferencesCursor
        INTO @referencingAssemblyName;

    END



        -- Drop the assembly

    SET @statement =      N'DROP ASSEMBLY'
                        + N' '
                        + N'[' + @AssemblyName + N']'
                        + N';'
                        ;

    EXEC sp_executesql @statement = @statement;


GO