October 3, 2013 | Posted in: SQL Server

Having looked for something that would drop an assemblies dependencies prior to dropping the assembly itself, I couldn’t find anything. So I put this together. Enjoy.

/* review schema information */
select  [schema].Name,
[modules].object_id,
[modules].assembly_id,
[modules].assembly_class,
[modules].assembly_method,
[objects].name,
[objects].type,
[objects].type_desc
from    sys.assembly_modules [modules]
join    sys.objects [objects] with (nolock)
on        [modules].object_id = [objects].object_id
join    sys.schemas [schema] with (nolock)
on        [objects].schema_id = [schema].schema_id
where    [modules].assembly_class in (N’NAME’)

begin try

begin transaction;

/* drop clr dependencies */
declare @dynamicSQL nvarchar(max)

declare @schema nvarchar(200)
declare @name nvarchar(200)
declare @type nvarchar(200)

declare db_cursor cursor for
select  [schema].Name,
[objects].name,
[objects].type_desc
from    sys.assembly_modules [modules]
join    sys.objects [objects] with (nolock)
on        [modules].object_id = [objects].object_id
join    sys.schemas [schema] with (nolock)
on        [objects].schema_id = [schema].schema_id
where    [modules].assembly_class in (N’NAME’)

open db_cursor
fetch next from db_cursor into @schema, @name, @type

while @@FETCH_STATUS = 0
begin
set @dynamicSQL = N”;
set @dynamicSQL = case @type
when N’CLR_STORED_PROCEDURE’ then N’drop procedure’ + N’ ‘ + @schema + N’.’ + @name + N’;’
when N’CLR_SCALAR_FUNCTION’ then N’drop function’ + N’ ‘ + @schema + N’.’ + @name + N’;’
when N’CLR_TABLE_VALUED_FUNCTION’ then N’drop function’ + N’ ‘ + @schema + N’.’ + @name + N’;’
else N’Object Not Recognised’
end

–print @dynamicSQL;
exec sp_executesql @dynamicSQL;

fetch next from db_cursor into @schema, @name, @type
end

close db_cursor
deallocate db_cursor

/* drop assembly once free of dependencies */
drop assembly [NAME]

if @@trancount > 0
commit transaction;

end try
begin catch

rollback transaction;
print ‘ERROR: ‘ + error_message()

end catch

Be the first to comment.

Leave a Reply

*


one × 2 =


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>