Recently I have had the pleasure of working on SQL Server 2008 R2 using the SQLCLR support.<\/p>\n
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.<\/p>\n
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..<\/p>\n
Enjoy…<\/p>\n<\/p>\n
\tdeclare mf_getprocs_cursor cursor local for \topen mf_getprocs_cursor \t\tif @type_desc = 'AGGREGATE_FUNCTION' \t\tif @type_desc = 'CLR_TRIGGER' \t\tif @type_desc = 'CLR_SCALAR_FUNCTION' \tdeclare mf_gettypes_cursor cursor local for \topen mf_gettypes_cursor \tif exists (select name from sys.assemblies where name=@assemblyName) <\/code><\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading
\nCREATE PROCEDURE MegaDrop @assemblyName varchar(max)
\nAS
\n\tdeclare @name varchar(max)
\n\tdeclare @type_desc varchar(max)<\/p>\n
\n\t SELECT so.name, so.type_desc from sys.objects so with (nolock)
\n\t\tJOIN sys.assembly_modules sam with (nolock) on so.object_id = sam.object_id
\n\t\tJOIN sys.assemblies sa with (nolock) on sam.assembly_id = sa.assembly_id
\n\t\tWHERE ( sa.name = @assemblyName)<\/p>\n
\n\tfetch next from mf_getprocs_cursor into @name, @type_desc
\n\twhile (@@fetch_status <> -1)
\n\tbegin
\n\t\tif @type_desc = 'CLR_STORED_PROCEDURE'
\n\t\tbegin
\n\t\t\tprint 'Executing DROP PROC '+@name + ' ('+@assemblyName+')'
\n\t\t\texecute ('drop proc '+@name)
\n\t\tend<\/p>\n
\n\t\tbegin
\n\t\t\tprint 'Executing DROP AGGREGATE '+@name + ' ('+@assemblyName+')'
\n\t\t\texecute ('drop Aggregate '+@name)
\n\t\tend<\/p>\n
\n\t\tbegin
\n\t\t\tprint 'Executing DROP TRIGGER '+@name + ' ('+@assemblyName+')'
\n\t\t\texecute ('drop trigger '+@name)
\n\t\tend<\/p>\n
\n\t\tbegin
\n\t\t\tprint 'Executing DROP FUNCTION '+@name + ' ('+@assemblyName+')'
\n\t\t\texecute ('drop function '+@name)
\n\t\tend
\n\t fetch next from mf_getprocs_cursor into @name, @type_desc
\n\tend
\n\tclose mf_getprocs_cursor
\n\tDEALLOCATE mf_getprocs_cursor <\/p>\n
\n\t SELECT so.name from sys.assembly_types so with (nolock)
\n\t\tJOIN sys.assemblies sa with (nolock) on so.assembly_id = sa.assembly_id
\n\t\tWHERE ( sa.name = @assemblyName)<\/p>\n
\n\tfetch next from mf_gettypes_cursor into @name
\n\twhile (@@fetch_status <> -1)
\n\tbegin
\n\t\tprint 'Executing DROP TYPE '+@name+' ('+@assemblyName+')'
\n\t\texecute ('drop type '+@name)
\n\t fetch next from mf_gettypes_cursor into @name
\n\tend
\n\tclose mf_gettypes_cursor
\n\tDEALLOCATE mf_gettypes_cursor <\/p>\n
\n\tbegin
\n\t\tprint 'Executing DROP ASSEMBLY ('+@assemblyName+')'
\n\t\texec ('drop assembly '+@assemblyName)
\n\tend<\/p>\n