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