create procedure sys.sp_MScleandbobjectsforreplication
as
begin
set nocount on
declare @tabid int
-- cleanup the objects in the database if they are not in clean state (replication metadata is marked)
-- check columns
declare @cdc_tracked_tables table (object_id int)
declare @cdc_table_id int
declare @quoted_object nvarchar(517)
declare @old_tabid int = 0
if object_id('cdc.change_tables') is not null
begin
insert @cdc_tracked_tables select distinct source_object_id from cdc.change_tables
end
begin tran
save tran tr_cleandbobj
if exists (select * from sys.columns where is_replicated = 1 or
is_non_sql_subscribed = 1 or is_dts_replicated = 1)
begin
DECLARE @colname sysname
DECLARE #hCclean_col CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT c.object_id, c.name, t.object_id from sys.columns c
left outer join @cdc_tracked_tables t on c.object_id = t.object_id
where c.is_replicated = 1 or c.is_non_sql_subscribed = 1
order by c.object_id
OPEN #hCclean_col
FETCH #hCclean_col into @tabid, @colname, @cdc_table_id
WHILE (@@fetch_status <> -1)
begin
-- Save a few extra queries here by only looking up the table name
-- and attempting to lock it once.
if @tabid <> @old_tabid
begin
EXEC sys.sp_MSget_qualified_name @tabid, @quoted_object OUTPUT
EXEC %%Object(MultiName = @quoted_object).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
if @@error <> 0
goto FAILURE
select @old_tabid = @tabid
end
if @cdc_table_id is null
begin
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplicated(Value = 0)
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetLogForRepl(Value = 0)
end
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetReplNonSQLSub(Value = 0)
EXEC %%ColumnEx(ObjectID = @tabid, Name = @colname).SetDTSReplicated(Value = 0)
FETCH #hCclean_col into @tabid, @colname, @cdc_table_id
end
CLOSE #hCclean_col
DEALLOCATE #hCclean_col
end
--check tables
if exists (select * from sys.tables where is_published = 1 or
is_schema_published = 1 or
is_replicated = 1 or
has_replication_filter = 1 )
begin
DECLARE #hCclean_obj CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT c.object_id, t.object_id from sys.tables c
left outer join @cdc_tracked_tables t on c.object_id = t.object_id
where c.is_published = 1 or
c.is_schema_published = 1 or
c.is_replicated = 1 or
c.has_replication_filter = 1
OPEN #hCclean_obj
FETCH #hCclean_obj into @tabid, @cdc_table_id
WHILE (@@fetch_status <> -1)
begin
EXEC sys.sp_MSget_qualified_name @tabid, @quoted_object OUTPUT
EXEC %%Object(MultiName = @quoted_object).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
if @@error <> 0
goto FAILURE
EXEC %%Object(ID = @tabid).SetPublished(Value = 0)
EXEC %%Object(ID = @tabid).SetSchemaPublished(Value = 0)
if @cdc_table_id is null
begin
EXEC %%Relation(ID = @tabid).SetReplicated(Value = 0)
EXEC %%Relation(ID = @tabid).SetHasFilterProc(Value = 0)
end
FETCH #hCclean_obj into @tabid, @cdc_table_id
end
CLOSE #hCclean_obj
DEALLOCATE #hCclean_obj
end
--check views, fn, proc
if exists (select * from sys.objects where (is_published = 1 or is_schema_published = 1 ))
begin
DECLARE #hCclean_obj CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT object_id from sys.objects where (is_published = 1 or
is_schema_published = 1)
OPEN #hCclean_obj
FETCH #hCclean_obj into @tabid
WHILE (@@fetch_status <> -1)
begin
EXEC sys.sp_MSget_qualified_name @tabid, @quoted_object OUTPUT
EXEC %%Object(MultiName = @quoted_object).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
if @@error <> 0
goto FAILURE
EXEC %%Object(ID = @tabid).SetPublished(Value = 0)
EXEC %%Object(ID = @tabid).SetSchemaPublished(Value = 0)
FETCH #hCclean_obj into @tabid
end
CLOSE #hCclean_obj
DEALLOCATE #hCclean_obj
end
--clear any 'proc exec' or 'serializable proc exec' bits
if exists (select * from sys.procedures where is_execution_replicated = 1 or is_repl_serializable_only = 1 )
begin
DECLARE #hCclean_obj CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT object_id from sys.procedures where (is_execution_replicated = 1 or
is_repl_serializable_only = 1)
OPEN #hCclean_obj
FETCH #hCclean_obj into @tabid
WHILE (@@fetch_status <> -1)
begin
EXEC sys.sp_MSget_qualified_name @tabid, @quoted_object OUTPUT
EXEC %%Object(MultiName = @quoted_object).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
if @@error <> 0
goto FAILURE
EXEC %%Module(ID = @tabid).SetProcReplicated(Value = 0)
EXEC %%Module(ID = @tabid).SetProcReplSerialOnly(Value = 0)
FETCH #hCclean_obj into @tabid
end
CLOSE #hCclean_obj
DEALLOCATE #hCclean_obj
end
commit tran
-- all done
return 0
FAILURE:
rollback tran tr_cleandbobj
commit tran
return (1)
end