Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSfixup_base_columns

  No additional text.


Syntax

-- Name: sp_MSfixup_base_columns

-- Owner: QunGuo

-- Description: this is a helper procedure which populates a temp
--			table with the base columns of all constraints/computed columns
--			added in current DDL, so to make it easier to figure out what
--			to post for each article, while we are here, why not also figure
--			out if we need to split into single command mode

-- Parameter:

-- Returns: 1 or 0   0 = success

-- Security: internal, not exposed
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSfixup_base_columns
(
--pass in these constant from caller, so we don't have define them everwhere
    @subtype_addcolumn int
    ,@subtype_adddf int
    ,@objid int
    ,@break_add_into_singles bit output
    ,@subtype int
    ,@subscription_active int
    ,@debug_print bit = 0
)
as
begin
	begin tran replddl_fixup_base_columns
	save tran replddl_fixup_base_columns

	--this query depend on ColumnName being valid so this has to happen after we fish out empty constraint names
	--I see no reason to qualify it with type of DDL, for drops it will simply insert nothing here

	--first get base column of all constraints added/enable/disabled in this DDL
	--exclud any newly added columns
	insert #basecol_cnst 	(constraint_column_id, constraint_column_name, constraint_name, type)
		select distinct sc.column_id, sc.name, fmcc.constraint_name, EnumType
			from sys.columns sc
					join sys.fn_MSconstraint_columns (@objid) fmcc on sc.column_id = fmcc.constraint_column_id
					join	#tran_altertable ta on fmcc.constraint_name = ta.ColumnName collate database_default
					where sc.object_id = @objid and sc.name not in
						(select ColumnName from #tran_altertable where EnumType = @subtype_addcolumn)
	if @@ERROR <> 0
	begin
		goto FAILURE
	end		

	--second get base column of all computed columns added in this DDL
	--exclude any column_id we add in first step
	--exclud any newly added columns
	insert #basecol_cnst 	(constraint_column_id, constraint_column_name, constraint_name, type)
		select distinct sc.column_id, sc.name, sc.name, EnumType
			from #tran_altertable
					full outer join sys.columns sc on sc.name = ColumnName collate database_default
					join sys.sql_dependencies ssd on sc.object_id = ssd.object_id and sc.column_id = ssd.referenced_minor_id
					where sc.object_id = @objid and sc.column_id is not null and sc.name is not null
						and sc.column_id not in (select constraint_column_id from #basecol_cnst)
						and sc.name not in
						(select ColumnName from #tran_altertable where EnumType = @subtype_addcolumn)
	if @@ERROR <> 0
	begin
		goto FAILURE
	end		
	if (@debug_print = 1)
		select 'stage' = 'sp_MSfixup_base_columns: const/computed', * from #basecol_cnst
		
	--the following logic has to do with figuring out if we need to split into single command mode
	--if it's already decided, just exit.
	if (@break_add_into_singles = 1)
		goto SKIPCHECK

	--in case of adding constraints to existing columns, if any base column is not in all artive partition
	--we can't post this cmd to everyone, switch to single cmd mode
	begin
		--these are the columns existing in every active partiton
		declare @artmincols table (column_id int)
		
		--article base on this object id with active subscription and allow replicate_ddl
		declare @artcols table (artid int)

		insert @artcols select distinct sa.artid from sysarticles sa
																	join syspublications sp on sp.pubid = sa.pubid
																	join syssubscriptions ss on ss.artid = sa.artid
																where sa.objid = @objid
																	and ss.status = @subscription_active  -- active
																	and sp.replicate_ddl = 1
																	

		--count of @artcols tells how many articles we may need to post to
		--group by having count() picks out colids exist in all of these articles.
		insert @artmincols (column_id) select sac.colid from dbo.sysarticlecolumns sac join
														@artcols ac on sac.artid = ac.artid
											group by sac.colid having count(sac.colid) = (select count(*) from @artcols)


		if exists (select * from #basecol_cnst where constraint_column_id not in (select column_id from @artmincols)) -- base col not in every partition
		begin
			set @break_add_into_singles = 1
		end
	end

	
	--if any column is added with named default 'with values', set so in ColumnAttr in addcolumn row
	
	update #tran_altertable set ColumnAttr = N'WITH VALUES' where ColumnName collate database_default in
			(select c.name collate database_default
				from #tran_altertable t 	join sys.default_constraints dc on dc.name = t.ColumnName collate database_default
									join sys.columns c on c.object_id = dc.parent_object_id
									and c.column_id = dc.parent_column_id
				where dc.parent_object_id = @objid and t.EnumType = @subtype_adddf
					and ColumnAttr = N'WITH VALUES' collate database_default)

	if @@ERROR <> 0
	begin
		goto FAILURE
	end		
					
	if @debug_print = 1
		select 'stage' = 'sp_MSfixup_base_columns: with values', * from #tran_altertable
	
	--if any default is added with base column in the same DDL, delete the default row now
	
	delete #tran_altertable where EnumType = @subtype_adddf and ColumnName collate database_default in
			(select dc.name collate database_default
				from #tran_altertable t
									join sys.columns c on c.name = t.ColumnName collate database_default
									join 	sys.default_constraints dc on c.object_id = dc.parent_object_id
																			and c.column_id = dc.parent_column_id
				where dc.parent_object_id = @objid and t.EnumType = @subtype_addcolumn)

	if @@ERROR <> 0
	begin
		goto FAILURE
	end		
					

	if @debug_print = 1
		select 'stage' = 'sp_MSfixup_base_columns: final', * from #tran_altertable


SKIPCHECK:
	commit tran replddl_fixup_base_columns
	return 0
FAILURE:
	rollback tran replddl_fixup_base_columns
	commit tran
	return 1

end

 
Last revision 2008RTM
See also

  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