- Microsoft SQL Server
- System tables
- Procedure
- Views
- Functions
|
sys.sp_MSremovedb_merge_replication_brute_force
Syntax
|
 |
 |
 |
|
/* 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
|
|
|
|
|
|
|
|
Last revision 2008RTM |
|
|
|
|
|
See also
News
|