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