-- Name: sp_MSbuild_single_post
-- Owner: QunGuo
-- Description: this is a helper procedure which is used by ddl trigger to
-- post single commands of drop fk/un/chk/df/column
-- base on article partition
-- before dropping column at subscriber, check and drop all column level default
-- if not included in original DDL cmd
-- Parameter:
-- Returns: 1 or 0 0 = success
-- Security: internal, not exposed
-- Requires Certificate signature for catalog access
create procedure sys.sp_MSbuild_single_post
(
@objid int
,@tran_artid int
,@tran_pub_options int = NULL
,@qual_dest_object nvarchar(1000) -- owner qualified destination table name
,@qual_p2p_conflict_table nvarchar(1000) = NULL -- owner qualified conflict table name
,@checkpartition_mask int = 0
,@checkadds_mask int = 0
,@subtype_dropcolumn int = 0
,@subtype_dropconstraint int = 0
,@subtype_enabletrigger int = 0
,@subtype_disabletrigger int = 0
,@subtype_enableconstraint int = 0
,@subtype_disableconstraint int = 0
,@subtype_addfk int = 0
,@subtype_altercolumn int = 0
,@drop_defaults_only bit = 0
,@dest_owner nvarchar(1000) = null
,@debug_print bit = 0
)
as
begin
declare @retcode int
,@colname nvarchar(1000)
,@subtype int
,@dropcolumn_list nvarchar(max) = N''
,@dropconstraint_list nvarchar(max) = N''
,@check_nocheck nvarchar(1000)
,@reftable nvarchar(1000)
,@refcollist nvarchar(max)
,@refcolcount int
,@stmt nvarchar(max)
,@stmtend nvarchar(1000)
,@ddlcmds nvarchar(max) = N''
,@alterp2pctcmd nvarchar(max) = NULL
,@column_id int
,@count_dropcnst int = 0
,@qual_colname nvarchar(1000)
,@p2p_cd_option int = 0x8
--get the list of columns and list of constraint being dropped in this DDL
if @debug_print = 1
select 'stage' = 'sp_MSbuild_single_post:enter'
,'@checkpartition_mask' = @checkpartition_mask
,'@objid' = @objid
,* from #tran_altertable
begin tran sp_MSbuild_single_post
save tran sp_MSbuild_single_post
--step1: drop default constraint before dropping a column,
--in case the default is created only on subscriber
--build a list of column name being dropped
--build a list of constraint name being dropped
DECLARE #listcursor CURSOR LOCAL FAST_FORWARD for
select quotename(ColumnName, ''''), EnumType from #tran_altertable
where EnumType in (@subtype_dropcolumn, @subtype_dropconstraint)
open #listcursor
fetch #listcursor into @colname, @subtype
while @@fetch_status <> -1
begin
if(@subtype = @subtype_dropcolumn)
select @dropcolumn_list = @dropcolumn_list + @colname + N','
else if (@subtype = @subtype_dropconstraint)
begin
select @dropconstraint_list = @dropconstraint_list + @colname + N','
select @count_dropcnst = @count_dropcnst + 1
end
fetch #listcursor into @colname, @subtype
end
close #listcursor
deallocate #listcursor
--remove trailing ',' when applicable
if len(@dropconstraint_list) > 1
select @dropconstraint_list = left(@dropconstraint_list, len(@dropconstraint_list) - 1)
if len(@dropcolumn_list) > 1
begin
select @dropcolumn_list = left(@dropcolumn_list, len(@dropcolumn_list) - 1)
--build script to drop default constraints for columns being dropped here
--only for the ones who's not being dropped explicitly here, use pre-yukon syntax
--to cover downlevel subs
select @ddlcmds = N'
declare @colname nvarchar(1000)
DECLARE #testcursor CURSOR LOCAL FAST_FORWARD for
select quotename(object_name(cdefault)) from syscolumns
where id = object_id(N''' + sys.fn_replreplacesinglequote(@qual_dest_object) + N''') and
cdefault is not null and cdefault > 0 and name in
(' + @dropcolumn_list + N')'
+ case len(@dropconstraint_list)
when 0 then N''
else N' and object_name(cdefault) not in (' + @dropconstraint_list + N')'
end + N'
open #testcursor
fetch #testcursor into @colname
while @@fetch_status <> -1
begin
exec (''alter table ' + sys.fn_replreplacesinglequote(@qual_dest_object) + N' drop constraint '' + @colname)
fetch #testcursor into @colname
end
close #testcursor
deallocate #testcursor
'
exec @retcode = sys.sp_MStran_autoproc @artid = @tran_artid
,@mode = 0
,@ddlcmd = @ddlcmds
if @retcode <>0 or @@ERROR<>0
goto FAILURE
end
if (@drop_defaults_only = 1)
goto FINISHED
--step2: drop constraints, to deal with self-referencing fk/un, fk needs to be dropped before un
--no need to special case unless we are dropping at least two constraints
if @count_dropcnst > 1
begin
--build script to drop fk before dropping other constraints (including un)
--only for the ones wo's being dropped explicitly here, use pre-yukon syntax
--to cover downlevel subs
select @ddlcmds = N'
DECLARE @cnst_name sysname
DECLARE #cnst CURSOR LOCAL FAST_FORWARD for
select quotename(object_name(constid)) from sysconstraints
where id = object_id(N''' + sys.fn_replreplacesinglequote(@qual_dest_object) + N''') and
objectproperty(constid, ''IsForeignKey'') = 1
and object_name(constid) in
(' + @dropconstraint_list + N')
open #cnst
fetch #cnst into @cnst_name
while @@fetch_status <> -1
begin
exec (''ALTER TABLE ' + sys.fn_replreplacesinglequote(@qual_dest_object) + N' DROP CONSTRAINT '' + @cnst_name)
fetch #cnst into @cnst_name
end
close #cnst
deallocate #cnst
DECLARE #cnst CURSOR LOCAL FAST_FORWARD for
select quotename(object_name(constid)) from sysconstraints
where id = object_id(N''' + sys.fn_replreplacesinglequote(@qual_dest_object) + N''') and
objectproperty(constid, ''IsForeignKey'') = 0
and object_name(constid) in
(' + @dropconstraint_list + N')
open #cnst
fetch #cnst into @cnst_name
while @@fetch_status <> -1
begin
exec (''ALTER TABLE ' + sys.fn_replreplacesinglequote(@qual_dest_object) + N' DROP CONSTRAINT '' + @cnst_name)
fetch #cnst into @cnst_name
end
close #cnst
deallocate #cnst
DECLARE #cnst CURSOR LOCAL FAST_FORWARD for
select quotename(name) from sysindexes
where id = object_id(N''' + sys.fn_replreplacesinglequote(@qual_dest_object) + N''')
and name in (' + @dropconstraint_list + N')
open #cnst
fetch #cnst into @cnst_name
while @@fetch_status <> -1
begin
exec (''DROP INDEX ' + sys.fn_replreplacesinglequote(@qual_dest_object) + N'.'' + @cnst_name)
fetch #cnst into @cnst_name
end
close #cnst
deallocate #cnst
'
exec @retcode = sys.sp_MStran_autoproc @artid = @tran_artid
,@mode = 0
,@ddlcmd = @ddlcmds
if @retcode <>0 or @@ERROR<>0
goto FAILURE
end
--step3: enable/disable constraints, add/drop/alter columns, add constraints
DECLARE #listcursor CURSOR LOCAL FAST_FORWARD for
--for enable/disable constraints, only post the ones where all base columns are in partition
--in theory the constraint with out-of-partition base columns should not have been replicated by us
--if user messes with it so that pub/sub has different constraints with the same name
--and expect enable/disable to work, it may or may not work, but entirely out of our control.
--for addun/fk/chk/df, only in partition ones should be posted
select distinct ColumnName, EnumType, check_nocheck, stmt, reftable, refcollist, refcolcount, stmtend, column_id
from #tran_altertable
where (((EnumType & @checkpartition_mask) > 0
or -- need to check partition if adding computed column
ColumnName in (select name from sys.computed_columns where object_id = @objid))
and inpartition = 1 )
or
--for drop column, dropconstraint, enabletrigger, disabletrigger,
--we really don't know whether the base is in partition or not, so don't bother to check
--for add/alter column no need to check partition neither, out of partition alter won't get here
((EnumType & @checkpartition_mask) = 0
and ColumnName not in (select name from sys.computed_columns where object_id = @objid))
order by EnumType, column_id
open #listcursor
fetch #listcursor into @colname, @subtype, @check_nocheck, @stmt, @reftable, @refcollist, @refcolcount, @stmtend, @column_id
while @@fetch_status <> -1
begin
if @debug_print = 1
select 'stage' = 'sp_MSbuild_single_post:cur'
,'@ddlcmds' = @ddlcmds
,'@colname' = @colname
,'@subtype' = @subtype
,'@check_nocheck' = @check_nocheck
,'@stmt' = @stmt
,'@reftable' = @reftable
,'@refcollist' = @refcollist
,'@refcolcount' = @refcolcount
,'@stmtend' = @stmtend
set @qual_colname = case len(isnull(@dest_owner, N'')) when 0 then N''
else quotename(@dest_owner) + N'.'
end
+ quotename(@colname)
--when dropping column
if (@subtype = @subtype_dropcolumn)
begin
select @ddlcmds = N'if exists (select * from syscolumns where id = object_id(N'''
+ sys.fn_replreplacesinglequote(@qual_dest_object) + ''') and name = N'
+ quotename (@colname, '''')+ N') ALTER TABLE '
+ @qual_dest_object
+ N' DROP COLUMN '
+ quotename(@colname)
if((@tran_pub_options is not null) and (@tran_pub_options & @p2p_cd_option) = @p2p_cd_option) and (@qual_p2p_conflict_table is not null)
begin
set @alterp2pctcmd = N'ALTER TABLE '
+ @qual_p2p_conflict_table
+ N' DROP COLUMN '
+ quotename(@colname);
exec(@alterp2pctcmd);
end
end
else if (@subtype = @subtype_addfk)
begin
select @ddlcmds = N'if exists (select si.indid from syscolumns sc '
+ N'join sysindexes si on sc.id = si.id '
+ N'join sysindexkeys sik on sik.id = sc.id and sik.indid = si.indid and sik.colid = sc.colid '
+ N'where sc.id = object_id(N'''
+ sys.fn_replreplacesinglequote(isnull(@reftable, @qual_dest_object))
+ N''') and sc.name in ('
+ @refcollist
+') and si.status & 2 = 2 group by si.indid having count(*) = '
+ cast (@refcolcount as nvarchar(12))
+N') ALTER TABLE '
+ @qual_dest_object + N' ' + @check_nocheck
+ N' ADD '
+ @stmt + isnull(@reftable, @qual_dest_object) + @stmtend
end
else if (@subtype & @checkadds_mask > 0)
begin
select @ddlcmds = case @subtype when @subtype_altercolumn then N'if exists (select * from syscolumns where id = object_id(N'''
+ sys.fn_replreplacesinglequote(@qual_dest_object) + ''') and name = N'
+ quotename (@colname, '''')+ N') '
else N'' end
+ N'ALTER TABLE '
+ @qual_dest_object + N' ' + @check_nocheck
+ case @subtype when @subtype_altercolumn then N' ALTER COLUMN '
else N' ADD '
end
+ @stmt
if((@tran_pub_options is not null) and (@tran_pub_options & @p2p_cd_option) = @p2p_cd_option) and (@qual_p2p_conflict_table is not null)
begin
set @alterp2pctcmd = N'ALTER TABLE '
+ @qual_p2p_conflict_table + N' ' + @check_nocheck
+ case @subtype when @subtype_altercolumn then N' ALTER COLUMN '
else N' ADD '
end
+ quotename(@colname)
+ N' '
+ sys.fn_gettypestring(@objid,@column_id,1, 0, 0, 0, 1, 0, 0, 0, 0)
+ N' NULL'
exec(@alterp2pctcmd)
end
end
else if (@subtype = @subtype_dropconstraint)
begin
--if we are dropping at least two consts, then step2 above should have handled it already
if @count_dropcnst > 1
goto MORECNST;
--slight optmization for dropping of one constraint
else
select @ddlcmds = N'if object_id(N'''
+ sys.fn_replreplacesinglequote(@qual_colname)+ N''') is not null ALTER TABLE '
+ @qual_dest_object
+ N' DROP CONSTRAINT '
+ quotename(@colname)
+ N' else if exists(select * from sysindexes where id = object_id(N'''
+ sys.fn_replreplacesinglequote(@qual_dest_object) + N''') and name = N'''
+ sys.fn_replreplacesinglequote(@colname)+ N''') '
+ N' DROP INDEX '
+ @qual_dest_object
+ N'.'
+ quotename(@colname)
end
else
begin
select @ddlcmds = N'if object_id(N'''
+ sys.fn_replreplacesinglequote(@qual_colname)+ N''') is not null ALTER TABLE '
+ @qual_dest_object
+ case @subtype when @subtype_enabletrigger then N' ENABLE TRIGGER '
when @subtype_disabletrigger then N' DISABLE TRIGGER '
when @subtype_enableconstraint then N' CHECK CONSTRAINT '
when @subtype_disableconstraint then N' NOCHECK CONSTRAINT '
end
+ quotename(@colname)
end
if @debug_print = 1
select 'stage' = 'sp_MSbuild_single_post:building'
,'@ddlcmds' = @ddlcmds
exec @retcode = sys.sp_MStran_autoproc @artid = @tran_artid
,@mode = 0
,@ddlcmd = @ddlcmds
if @retcode <>0 or @@ERROR<>0
goto DROPTRAN
MORECNST:
fetch #listcursor into @colname, @subtype, @check_nocheck, @stmt, @reftable, @refcollist, @refcolcount, @stmtend, @column_id
end
close #listcursor
deallocate #listcursor
FINISHED:
commit tran sp_MSbuild_single_post
if @debug_print = 1
select 'stage' = 'sp_MSbuild_single_post:final'
, '@ddlcmds' = @ddlcmds
return 0
DROPTRAN:
close #listcursor
deallocate #listcursor
FAILURE:
rollback tran sp_MSbuild_single_post
commit
return 1
end
