Recently I have had the pleasure of working on SQL Server 2008 R2 using the SQLCLR support.
Which I must admit it is quite nice to use, though a couple of things have anoyed me while using… one of which is the user of “DROP ASSEMBLY” and its dependancies…. normally this is not a huge problem but when you are write tests, it is good practice to remove/drop everything before deploying.
Being the sort of person that hates doing repetitively, I thought I would do a quick proc…. so let me introduce you to ‘MegaDrop’… yeah… its a rubbish name and the code is not elegant but it gets the job done..
Enjoy…
CREATE PROCEDURE MegaDrop @assemblyName varchar(max)
AS
declare @name varchar(max)
declare @type_desc varchar(max)
declare mf_getprocs_cursor cursor local for
SELECT so.name, so.type_desc from sys.objects so with (nolock)
JOIN sys.assembly_modules sam with (nolock) on so.object_id = sam.object_id
JOIN sys.assemblies sa with (nolock) on sam.assembly_id = sa.assembly_id
WHERE ( sa.name = @assemblyName)
open mf_getprocs_cursor
fetch next from mf_getprocs_cursor into @name, @type_desc
while (@@fetch_status <> -1)
begin
if @type_desc = 'CLR_STORED_PROCEDURE'
begin
print 'Executing DROP PROC '+@name + ' ('+@assemblyName+')'
execute ('drop proc '+@name)
end
if @type_desc = 'AGGREGATE_FUNCTION'
begin
print 'Executing DROP AGGREGATE '+@name + ' ('+@assemblyName+')'
execute ('drop Aggregate '+@name)
end
if @type_desc = 'CLR_TRIGGER'
begin
print 'Executing DROP TRIGGER '+@name + ' ('+@assemblyName+')'
execute ('drop trigger '+@name)
end
if @type_desc = 'CLR_SCALAR_FUNCTION'
begin
print 'Executing DROP FUNCTION '+@name + ' ('+@assemblyName+')'
execute ('drop function '+@name)
end
fetch next from mf_getprocs_cursor into @name, @type_desc
end
close mf_getprocs_cursor
DEALLOCATE mf_getprocs_cursor
declare mf_gettypes_cursor cursor local for
SELECT so.name from sys.assembly_types so with (nolock)
JOIN sys.assemblies sa with (nolock) on so.assembly_id = sa.assembly_id
WHERE ( sa.name = @assemblyName)
open mf_gettypes_cursor
fetch next from mf_gettypes_cursor into @name
while (@@fetch_status <> -1)
begin
print 'Executing DROP TYPE '+@name+' ('+@assemblyName+')'
execute ('drop type '+@name)
fetch next from mf_gettypes_cursor into @name
end
close mf_gettypes_cursor
DEALLOCATE mf_gettypes_cursor
if exists (select name from sys.assemblies where name=@assemblyName)
begin
print 'Executing DROP ASSEMBLY ('+@assemblyName+')'
exec ('drop assembly '+@assemblyName)
end
AS
declare @name varchar(max)
declare @type_desc varchar(max)
declare mf_getprocs_cursor cursor local for
SELECT so.name, so.type_desc from sys.objects so with (nolock)
JOIN sys.assembly_modules sam with (nolock) on so.object_id = sam.object_id
JOIN sys.assemblies sa with (nolock) on sam.assembly_id = sa.assembly_id
WHERE ( sa.name = @assemblyName)
open mf_getprocs_cursor
fetch next from mf_getprocs_cursor into @name, @type_desc
while (@@fetch_status <> -1)
begin
if @type_desc = 'CLR_STORED_PROCEDURE'
begin
print 'Executing DROP PROC '+@name + ' ('+@assemblyName+')'
execute ('drop proc '+@name)
end
if @type_desc = 'AGGREGATE_FUNCTION'
begin
print 'Executing DROP AGGREGATE '+@name + ' ('+@assemblyName+')'
execute ('drop Aggregate '+@name)
end
if @type_desc = 'CLR_TRIGGER'
begin
print 'Executing DROP TRIGGER '+@name + ' ('+@assemblyName+')'
execute ('drop trigger '+@name)
end
if @type_desc = 'CLR_SCALAR_FUNCTION'
begin
print 'Executing DROP FUNCTION '+@name + ' ('+@assemblyName+')'
execute ('drop function '+@name)
end
fetch next from mf_getprocs_cursor into @name, @type_desc
end
close mf_getprocs_cursor
DEALLOCATE mf_getprocs_cursor
declare mf_gettypes_cursor cursor local for
SELECT so.name from sys.assembly_types so with (nolock)
JOIN sys.assemblies sa with (nolock) on so.assembly_id = sa.assembly_id
WHERE ( sa.name = @assemblyName)
open mf_gettypes_cursor
fetch next from mf_gettypes_cursor into @name
while (@@fetch_status <> -1)
begin
print 'Executing DROP TYPE '+@name+' ('+@assemblyName+')'
execute ('drop type '+@name)
fetch next from mf_gettypes_cursor into @name
end
close mf_gettypes_cursor
DEALLOCATE mf_gettypes_cursor
if exists (select name from sys.assemblies where name=@assemblyName)
begin
print 'Executing DROP ASSEMBLY ('+@assemblyName+')'
exec ('drop assembly '+@assemblyName)
end