Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSbuild_single_post

  No additional text.


Syntax

-- 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

 
Last revision 2008RTM
See also

  sp_MSfixup_single_ddls (Procedure)
sp_MStran_altertable (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash