/* Permission to sysadmin - Wrapper to include security check*/
create procedure sys.sp_MSremovedb_merge_replication_brute_force as
begin

		declare @merge_object nvarchar(517)
		declare @schema_name sysname
		declare @object_type char(2)
		declare @constraintname nvarchar(258)
		declare @parent_object_id int
		declare @parent_table nvarchar(517)
		DECLARE @retcode int


		-- drop objects that are marked is_ms_shipped and are named MSmerge_%	
		declare merge_objects cursor local fast_forward
		for select QUOTENAME(name), type from sys.objects
			where (name like 'MSmerge_%' or  name like  'sysmerge_%') and is_ms_shipped=1
		open merge_objects
		fetch merge_objects into @merge_object, @object_type
		while @@fetch_status <> -1
		begin
			if @object_type = 'P'
				exec ('drop procedure ' + @merge_object)				
			else if @object_type = 'V'
				exec ('drop view ' + @merge_object)			
			else if @object_type = 'U'
				exec ('drop table ' + @merge_object)				
			else if @object_type = 'TR'
				exec ('drop trigger ' + @merge_object)			
			else if @object_type = 'D'
				exec ('drop constraint ' + @merge_object)
			fetch merge_objects into @merge_object, @object_type
		end
		close merge_objects
		deallocate merge_objects				



		-- drop database level DDL triggers
		declare merge_ddl_triggers cursor local fast_forward
		for select QUOTENAME(name) from sys.triggers
			where name like 'MSmerge_%' and is_ms_shipped=1
		open merge_ddl_triggers
		fetch merge_ddl_triggers into @merge_object
		while @@fetch_status <> -1
		begin
			exec ('drop trigger ' + @merge_object)		
			fetch merge_ddl_triggers into @merge_object
		end
		close merge_ddl_triggers
		deallocate merge_ddl_triggers	
	
		-- drop triggers for tables under nondbo schemas.
		-- Triggers for user.table articles cannot be marked is_ms_shipped			
	    declare merge_nondbo_triggers cursor local fast_forward
	    for select QUOTENAME(trgs.name), SCHEMA_NAME(objs.schema_id) from sys.triggers trgs JOIN sys.objects objs ON trgs.parent_id = objs.object_id
	        where trgs.name like 'MSmerge_%'
	    open merge_nondbo_triggers
	    fetch merge_nondbo_triggers into @merge_object, @schema_name
	    while @@fetch_status <> -1
	    begin
	        select @merge_object = QUOTENAME(@schema_name) + '.' + @merge_object
			exec ('drop trigger ' + @merge_object)            			
	        fetch merge_nondbo_triggers into @merge_object, @schema_name
	    end
	    close merge_nondbo_triggers
	    deallocate merge_nondbo_triggers	

	    -- remove all repl_identity_range constraints
	    declare idrange_constraints cursor local fast_forward
	    for select quotename(name), parent_object_id from sys.objects where name like 'repl_identity_range%' and type = 'C'
	    open idrange_constraints
	    fetch idrange_constraints into @constraintname, @parent_object_id
	    while @@fetch_status <> -1
	    begin
	        select @parent_table = quotename(schema_name(schema_id)) + '.' + quotename(name) from sys.objects where object_id = @parent_object_id
			exec ('alter table ' + @parent_table + ' drop constraint ' + @constraintname)				
    	    fetch next from idrange_constraints into @constraintname, @parent_object_id
	    end
	    close idrange_constraints
	    deallocate idrange_constraints	

		-- drop all kind of misc tables
		declare @misc_tables_list table(name sysname)
		insert into @misc_tables_list values('MSdynamicsnapshotjobs')
		insert into @misc_tables_list values('MSdynamicsnapshotviews')	
		declare misc_tables cursor local fast_forward
		for select QUOTENAME(o.name) from sys.objects o, @misc_tables_list as m
			where o.name = m.name and o.type = 'U' and o.is_ms_shipped=1

		open misc_tables
		fetch misc_tables into @merge_object
		while @@fetch_status <> -1
		begin
			exec ('drop table ' + @merge_object)
			 fetch next from misc_tables into @merge_object					
		end
		close misc_tables
	    deallocate misc_tables	
end