Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


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

		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
			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
		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
			exec ('drop trigger ' + @merge_object)		
			fetch merge_ddl_triggers into @merge_object
		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
	        select @merge_object = QUOTENAME(@schema_name) + '.' + @merge_object
			exec ('drop trigger ' + @merge_object)            			
	        fetch merge_nondbo_triggers into @merge_object, @schema_name
	    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
	        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
	    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
			exec ('drop table ' + @merge_object)
			 fetch next from misc_tables into @merge_object					
		close misc_tables
	    deallocate misc_tables	

Last revision 2008RTM
See also

  sp_removedbreplication (Procedure)


  Query a named instance
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash