/* this proc will drop all the per article contents views if they
   exist for the given publication */
create procedure sys.sp_MSdropctsviews
    @pubid uniqueidentifier,
    @artid uniqueidentifier = NULL
AS
    declare @ctsview              sysname
    declare @ctsview_80           sysname
    declare @ctsview_90_forall    sysname
    declare @ctsview_90_forglobal sysname
    declare @ctsview_rowtrack sysname
    declare @pubidstr       nvarchar(40)
    declare @artidstr       nvarchar(40)
    declare @retcode int
    declare @tablenick  int

    exec @retcode = sys.sp_MSguidtostr @pubid, @pubidstr out
    if @@ERROR <>0 OR @retcode <> 0
        return (1)

    if @artid is NULL
    begin
        select @tablenick = min(nickname) from dbo.sysmergearticles where pubid = @pubid
        while @tablenick is not null
        begin
            select @artid = artid from dbo.sysmergearticles where nickname = @tablenick and pubid = @pubid
            exec @retcode = sys.sp_MSguidtostr @artid, @artidstr out
            if @@ERROR <>0 OR @retcode <> 0 return (1)

            set @ctsview = 'MSmerge_cont_' + @pubidstr + '_' + @artidstr
            set @ctsview_90_forall = 'MSmerge_cont_90_forall_' + @pubidstr + '_' + @artidstr
            set @ctsview_90_forglobal = 'MSmerge_cont_90_forglobal_' + @pubidstr + '_' + @artidstr
            set @ctsview_80 = 'MSmerge_cont_' + @pubidstr + '_' + @artidstr + '_80'
            set @ctsview_rowtrack = 'MSmerge_cont_rowtrack_' + @pubidstr + '_' + @artidstr

            exec @retcode = sp_MSdropview_internal @ctsview
            if @@ERROR<>0 OR @retcode<>0 return (1)

            exec @retcode = sp_MSdropview_internal @ctsview_90_forall
            if @@ERROR<>0 OR @retcode<>0 return (1)

            exec @retcode = sp_MSdropview_internal @ctsview_90_forglobal
            if @@ERROR<>0 OR @retcode<>0 return (1)

            exec @retcode = sp_MSdropview_internal @ctsview_80
            if @@ERROR<>0 OR @retcode<>0 return (1)

            exec @retcode = sp_MSdropview_internal @ctsview_rowtrack
            if @@ERROR<>0 OR @retcode<>0 return (1)

            /* Advance to next article and repeat the loop */
            select @tablenick = min(nickname) from dbo.sysmergearticles where pubid = @pubid and nickname > @tablenick
        end
    end
    else
    begin
        exec @retcode = sys.sp_MSguidtostr @artid, @artidstr out
        if @@ERROR <>0 OR @retcode <> 0 return (1)

        set @ctsview = 'MSmerge_cont_' + @pubidstr + '_' + @artidstr
        set @ctsview_90_forall = 'MSmerge_cont_90_forall_' + @pubidstr + '_' + @artidstr
        set @ctsview_90_forglobal = 'MSmerge_cont_90_forglobal_' + @pubidstr + '_' + @artidstr
        set @ctsview_80 = 'MSmerge_cont_' + @pubidstr + '_' + @artidstr + '_80'
        set @ctsview_rowtrack = 'MSmerge_cont_rowtrack_' + @pubidstr + '_' + @artidstr

        exec @retcode = sp_MSdropview_internal @ctsview
        if @@ERROR<>0 OR @retcode<>0 return (1)

        exec @retcode = sp_MSdropview_internal @ctsview_90_forall
        if @@ERROR<>0 OR @retcode<>0 return (1)

        exec @retcode = sp_MSdropview_internal @ctsview_90_forglobal
        if @@ERROR<>0 OR @retcode<>0 return (1)

        exec @retcode = sp_MSdropview_internal @ctsview_80
        if @@ERROR<>0 OR @retcode<>0 return (1)

        exec @retcode = sp_MSdropview_internal @ctsview_rowtrack
        if @@ERROR<>0 OR @retcode<>0 return (1)
    end

    return 0