--AJ: Variable for the assembly we want to target
DECLARE @AssemblyName nvarchar(512)
 
--AJ: Set the value for testing purposes
SET @AssemblyName = 'premiersoftware.sqlserver.spaplus2, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil'
 
--AJ: Declare our cursor
--AJ: We just want the rows to read and at full pelt!
DECLARE crs_CLRStoredProcs CURSOR FAST_FORWARD read_only FOR
        (
                --AJ: Define the rows we want
                SELECT sys.procedures.name
                FROM sys.procedures, 
                        sys.assembly_modules,
                        sys.assemblies
                WHERE sys.procedures.type = 'PC'
                        AND sys.procedures.object_id = sys.assembly_modules.object_id
                        AND sys.assembly_modules.assembly_id = sys.assemblies.assembly_id
                        AND sys.assemblies.clr_name LIKE @AssemblyName
        )
 
 
OPEN crs_CLRStoredProcs
--AJ: For retriving the stroed proc names
DECLARE @StoredProcName nvarchar(256)
--AJ: Retrive the stroed proc names
FETCH NEXT FROM crs_CLRStoredProcs INTO @StoredProcName
--AJ: Make sure theres no errors
WHILE @@fetch_status = 0
        BEGIN
        PRINT 'DROP PROCEDURE ' + @StoredProcName

FETCH NEXT FROM crs_CLRStoredProcs INTO @StoredProcName <---------- DOH!
        END 

--AJ: Clean up after ourselves
CLOSE crs_CLRStoredProcs
DEALLOCATE crs_CLRStoredProcs


--SELECT *
--FROM sys.assemblies