Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSfixup_single_ddls

  No additional text.


Syntax

-- Name: sp_MSfixup_single_ddls

-- Owner: QunGuo

-- Description: this is a helper procedure which is used by ddl trigger to
--		reconstruct individual ddl cmd, more specifically, it updates
--		#tran_altertable with more details corresponding to each DDL
--		we end up posting, so that subsequent loop can pick out the ones of
--		interest to post base on article info

--		note this proc only fill the table with none-article specific entries,
--		addcolumn/alter need article information (such as type mapping),
--		hence coverred in sys.sp_MSfixup_single_artddls
--		
--		addfk is partially handled here, then completed in sp_MSbuild_single_post

-- Parameter:

-- Returns: 1 or 0   0 = success

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

create procedure sys.sp_MSfixup_single_ddls
(
@objid int
,@subtype_addcolumn int
,@subtype_addun int
,@subtype_addchk int
,@subtype_adddf int
,@subtype_addfk int
,@debug_print bit = 0
)
as
begin
	declare @stmt nvarchar(max)
				,@reftable nvarchar(512)
				,@refcollist nvarchar(max)
				,@colname nvarchar(512)
				,@stmtend nvarchar(512)
				,@refcolcount int
				,@referenced_object_id int
	
	if @debug_print = 1
		select 'stage' = 'sp_MSfixup_single_ddls:enter'
					,'comments=' = 'constraint names filled'
					, * from #tran_altertable

	begin tran replddl_fixup_single_cmds
	save tran replddl_fixup_single_cmds

	--skip inserting of regular addcolumn here, it will be done within article loop
	--because each article may have different setting for map to base types
	

	--fish out individual 'add unique constraint' in current DDL
	--index options are not replicated over
	update #tran_altertable set
			 check_nocheck = N''
			,stmt = sys.fn_MStran_unique(@objid, quotename(ColumnName))
			where EnumType = @subtype_addun
	if @@ERROR <> 0
	begin
		goto FAILURE
	end		

	--fish out individual 'add default' in current DDL
	--exclude defaults added for new column cuz this is covered in 'addcolumn'
	update #tran_altertable set
		check_nocheck = N''
		, stmt = N'CONSTRAINT ' + quotename(dc.name) + N' DEFAULT ' + definition + N' FOR ' + quotename(c.name) +
			case t.ColumnAttr collate database_default  when N'WITH VALUES' then N' WITH VALUES'
												else N''
												end
		from
		sys.default_constraints dc join sys.columns c on c.object_id = dc.parent_object_id
	   										 and c.column_id = dc.parent_column_id
		join #tran_altertable t on dc.name = t.ColumnName collate database_default
		where parent_object_id = @objid  and t.EnumType = @subtype_adddf
			and c.name not in (select ColumnName collate database_default from #tran_altertable
										where EnumType = @subtype_addcolumn)
	if @@ERROR <> 0
	begin
		goto FAILURE
	end		

	--fish out individual 'add check constraint' in current DDL
	update #tran_altertable set
		check_nocheck = case is_not_trusted when 1 then N' WITH NOCHECK ' else N'' end
		,stmt = N'CONSTRAINT ' + quotename(name) + N' CHECK NOT FOR REPLICATION ' + definition
		from sys.check_constraints
		join #tran_altertable t on name = t.ColumnName collate database_default
		where parent_object_id = @objid and t.EnumType = @subtype_addchk
	if @@ERROR <> 0
	begin
		goto FAILURE
	end		

	--fish out individual 'add computed column' in current DDL
	update #tran_altertable set
		stmt = quotename(scc.name) + N' AS ' + scc.definition,
		column_id = scc.column_id
		from
		sys.computed_columns scc
			join #tran_altertable t on scc.name = t.ColumnName collate database_default
		where scc.object_id = @objid and t.EnumType = @subtype_addcolumn
	if @@ERROR <> 0
	begin
		goto FAILURE
	end		

	--finsh out 'add foreign key' here, it will be modified within article loop again
	--because each article may have different setting for dest_table/dest_owner
	
	DECLARE #fkcursor CURSOR LOCAL FAST_FORWARD for
			select  quotename(ColumnName) ,f.referenced_object_id
					from #tran_altertable t
					join sys.foreign_keys f on f.name = t.ColumnName collate database_default
					where f.parent_object_id = @objid
							and EnumType = @subtype_addfk

		open #fkcursor
		fetch #fkcursor into @colname, @referenced_object_id
		while @@fetch_status <> -1
		begin
			select @stmt = stmt, @reftable = reftable, @refcollist = refcollist, @stmtend = stmtend, @refcolcount = refcolcount
				from sys.fn_MStran_foreignkey(@objid, @referenced_object_id, @colname)

			if @debug_print = 1
				select 'stage' = 'sp_MSfixup_single_ddls:fk'
										,'@colname' = @colname
										,'@referenced_object_id' = @referenced_object_id
										,'@stmt' = @stmt
										,'@reftable' = @reftable
										,'@refcollist' = @refcollist
										,'@refcolcount' = @refcolcount
										,'@stmtend' = @stmtend

			update #tran_altertable
					set stmt = @stmt, reftable = @reftable, refcollist = @refcollist, stmtend = @stmtend, refcolcount = @refcolcount
					where quotename(ColumnName) = @colname collate database_default
			if @@ERROR <> 0
			begin
				goto DROPTRAN
			end		

			fetch #fkcursor into @colname, @referenced_object_id
		end
		close #fkcursor
		deallocate #fkcursor

		if @debug_print = 1
			select 'stage' = 'sp_MSfixup_single_ddls:complete'
						,'comments' = 'rebuild un/chk/df(some)'
						, * from #tran_altertable

		commit tran replddl_fixup_single_cmds
		return 0

DROPTRAN:
	close #fkcursor
	deallocate #fkcursor
FAILURE:
	rollback tran replddl_fixup_single_cmds
	commit tran
	return 1
end

 
Last revision 2008RTM
See also

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