create procedure sys.sp_MShelpalterbeforetable
@objid int,
@biname sysname
AS
declare @command nvarchar(4000)
declare @retcode int
declare @include int
declare @tablenick int
declare @colname nvarchar(258)
declare @typename nvarchar(258)
declare @base_typename sysname
declare @schname nvarchar(259)
declare @system_type_id int
declare @colid smallint
declare @colidstr nvarchar(5)
declare @len smallint
declare @prec smallint
declare @scale int
declare @isnullable tinyint
declare @bi_objid int
set nocount on
declare @cMaxIndexLength int
set @cMaxIndexLength= 900 -- max index column size in SQL 2000
select @tablenick = max(nickname) from dbo.sysmergearticles where objid = @objid
if @tablenick is null
return (1)
select @bi_objid = OBJECT_ID(quotename(@biname))
declare @role sysname
declare @pubid uniqueidentifier
declare publications_list CURSOR LOCAL FAST_FORWARD
for select p.pubid from dbo.sysmergearticles a, dbo.sysmergepublications p
where objid = @objid and p.pubid = a.pubid and UPPER(p.publisher) = UPPER(publishingservername()) collate database_default and p.publisher_db = db_name()
open publications_list
fetch publications_list into @pubid
while @@fetch_status <> -1
begin
exec @retcode = dbo.sp_MSrepl_FixPALRole @pubid, @role output
if @retcode <> 0 or @@ERROR<>0
goto errlabel
select @command = 'grant select (system_delete, generation), update(generation), delete, insert on ' + quotename(@biname) + ' to ' + quotename(@role)
exec (@command)
if @@ERROR<>0
goto errlabel
fetch publications_list into @pubid
end
close publications_list
deallocate publications_list
-- Loop over the columns and see which ones we include
declare col_cursor CURSOR LOCAL FAST_FORWARD
for select C.name, type_name(C.user_type_id), type_name(C.system_type_id),
case when S.name<>'sys' and S.name<>'dbo'
then QUOTENAME(S.name)+'.' else '' end,
C.system_type_id, C.max_length, C.precision, C.scale, C.is_nullable, C.column_id
from sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
where C.object_id = @objid and C.is_computed <> 1 and C.user_type_id <> type_id('timestamp') order by C.column_id
FOR READ ONLY
open col_cursor
fetch next from col_cursor into @colname, @typename, @base_typename, @schname, @system_type_id, @len, @prec, @scale, @isnullable, @colid
while (@@fetch_status <> -1)
begin
set @include = 0
if not exists (select * from sys.columns where object_id = @bi_objid and QUOTENAME(name) = QUOTENAME('system_delete'))
begin
set @command = 'alter table ' + quotename(@biname) + ' ADD system_delete bit default(0) '
execute ( @command )
if @@ERROR<>0
goto errlabel
/* grant select to system_delete column */
set @command = 'grant select (system_delete) on ' + quotename(@biname) + ' to public'
exec (@command)
if @@ERROR<>0
goto errlabel
end
-- Is this column already in the before image table?
if exists (select * from sys.columns where object_id = @bi_objid and name = @colname)
begin
goto fetchnext
end
-- does updating this column change membership in a partial replica?
if exists (select * from dbo.sysmergearticles
where objid = @objid and sys.fn_MSisfilteredcolumn(subset_filterclause, @colname, @objid) = 1)
set @include = 1
else if exists (select * from dbo.sysmergesubsetfilters
where art_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @objid) = 1)
set @include = 1
else if exists (select * from dbo.sysmergesubsetfilters
where join_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @objid) = 1)
set @include = 1
-- If we want this column, map its type and insert a row to temp table
if @include <> 1
begin
goto fetchnext
end
if ((@base_typename='nvarchar' or @base_typename='nchar') and @len <> -1) -- a unit of nchar takes 2 bytes
set @len = @len/2
exec @retcode = sys.sp_MSmaptype @typename out, @len, @prec, @scale
if @@ERROR<>0 or @retcode<>0
goto errlabel
if (sys.fn_IsTypeBlob(@system_type_id, @len) = 0)
begin
set @colname = QUOTENAME(@colname)
-- Always make columns nullable when we add them because we might have
-- existing rows in the before image table.
set @command = 'alter table ' + quotename(@biname) + ' ADD ' + @colname + ' ' + @schname + @typename + ' NULL '
execute ( @command )
if @@ERROR<>0 goto errlabel
-- Insert a create index command if column is not too long
if (@len <= @cMaxIndexLength)
begin
set @colidstr =convert(nvarchar(4), @colid)
set @command = 'create index ' + quotename(@biname + '_' + @colidstr) + ' on ' + quotename(@biname) + ' (' + @colname + ')'
execute ( @command )
if @@ERROR<>0 goto errlabel
end
end
fetchnext:
/* Repeat the loop with next column */
fetch next from col_cursor into @colname, @typename, @base_typename, @schname, @system_type_id, @len, @prec, @scale, @isnullable, @colid
end
close col_cursor
deallocate col_cursor
return 0
errlabel:
close col_cursor
deallocate col_cursor
return 1