create procedure sys.sp_MSAdjustConflictTable(
@qual_source_object nvarchar(540),
@pubid uniqueidentifier,
@columnName sysname,
@column_change_type int = 1 --addcolumn 1, dropcolumn 2, alter column 3
)
AS
declare @artname sysname
declare @retcode int
declare @objid int
declare @con_id int
declare @conflict_table sysname
declare @ins_conflict_proc sysname
declare @qual_column nvarchar(258)
declare @len int
declare @prec int
declare @scale int
declare @typename nvarchar(270)
declare @command nvarchar(4000)
declare @create_unq nvarchar(500)
declare @is_filestream int
declare @rowguidcol sysname
select @qual_column=QUOTENAME(@columnName)
select @objid = object_id(@qual_source_object)
select @conflict_table=conflict_table from dbo.sysmergearticles where pubid=@pubid and objid=@objid
if @conflict_table is null
return 0
select @con_id = object_id(quotename(@conflict_table))
if @con_id is NULL
begin
raiserror(21286, 16, -1, @conflict_table)
return (1)
end
-- add/alter/drop column in conflict table
set @command = NULL
if (@column_change_type = 1 or @column_change_type = 3) and @conflict_table is not NULL
begin
--figure out the type of newly added column and add it to the conflict table
select @typename = NULL
select @typename = case system_type_id when 240 then type_name(user_type_id) else type_name(system_type_id) end,
@len = max_length, @prec = precision, @scale = scale, @is_filestream = is_filestream
from sys.columns where name=@columnName and object_id=@objid and is_computed<>1 and system_type_id <> type_id('timestamp')
if @typename is NULL
return (0)
if @typename='nvarchar' or @typename='nchar' -- a unit of nchar takes 2 bytes
select @len = @len/2
exec @retcode = sys.sp_MSmaptype @typename out, @len, @prec, @scale
if @@error<>0 OR @retcode <>0
return (1)
if @column_change_type = 1 and not exists (select * from sys.columns where object_id = @con_id and name=@columnName)
select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' add ' + @qual_column + ' ' + @typename
else if @column_change_type = 3 and exists (select * from sys.columns where object_id = @con_id and name=@columnName)
select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' alter column ' + @qual_column + ' ' + @typename
-- add filestream keyword for Filestream columns
if @is_filestream = 1
begin
-- check if NON-NULL, unique rowguid col exists on conflict table
-- is rowguidcol nullable?
if not exists( select * from sys.columns where object_id= @con_id and is_rowguidcol = 1 and is_nullable = 0 )
begin
-- cannot alter rowguid col to be non-nullable
return (1)
end
-- is rowguidcol unique?
if not exists (
select i.index_id from sys.indexes i
join sys.index_columns icol on icol.object_id = i.object_id and icol.index_id = i.index_id
where i.object_id = @con_id and i.is_unique_constraint = 1
and columnproperty(i.object_id, col_name(i.object_id, icol.column_id), 'isrowguidcol')=1
and indexproperty(i.object_id, i.name, 'userkeycount') = 1
)
begin
-- unique index on ROWGUIDCOL does not exist. create one.
select @rowguidcol = name from sys.columns where object_id= @con_id and is_rowguidcol = 1
select @create_unq = 'alter table ' + QUOTENAME(@conflict_table) + ' add unique (' + quotename(@rowguidcol) + ')'
exec(@create_unq)
if @@ERROR<>0
return (1)
end
select @command = @command + ' FILESTREAM '
end
select @command = @command + ' NULL '
if @command is not NULL
begin
exec(@command)
if @@ERROR<>0
return (1)
end
end
else if @column_change_type = 2 and @conflict_table is not NULL and exists (select * from sys.columns where object_id = @con_id and name=@columnName)
begin
select @command = 'alter table ' + QUOTENAME(@conflict_table) + ' drop column ' + @qual_column
exec(@command)
if @@ERROR<>0
return (1)
end
return (0)