Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- Name: sp_MSfixup_single_artddls

-- Owner: QunGuo

-- Description: this is a helper procedure which is used by ddl trigger to
--		reconstruct individual ddl cmd per article, more specifically,
--		it covers add/alter of non-computed columns
--		everything eles are covered in sp_MSfixup_single_ddls

-- Parameter:

-- Returns: 1 or 0   0 = success

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

create procedure sys.sp_MSfixup_single_artddls
@objid int
,@schema_option bigint
,@subtype_altercolumn int
,@subtype_addcolumn int
,@debug_print bit = 0
	begin tran replddl_fixup_single_artddls
	save tran replddl_fixup_single_artddls

	if @debug_print = 1
	select 'stage' = 'sp_MSfixup_single_artddls:enter'
				,'comments=' = 'have un/fk/chk/df(some)/computed col, but but not addcol/altercol'
				, * from #tran_altertable
	--update newly added columns entry now
	update #tran_altertable set
		stmt = quotename(c.name) + N' ' + sys.fn_gettypestring(c.object_id
								    ,case (@schema_option & 0x0000000000000020) when 0x0000000000000020 then 1 else 0 end
									,case (@schema_option & 0x0000000010000000) when 0x0000000010000000 then 1 else 0 end
									,case (@schema_option & 0x0000000020000000) when 0x0000000020000000 then 1 else 0 end
									,case (@schema_option & 0x0000000000000008) when 0x0000000000000008 then 0 else 1 end
                                    					,case (@schema_option & 0x0000000200000000) when 0x0000000200000000 then 1 else 0 end
									,case (@schema_option & 0x0000002000000000) when 0x0000002000000000 then 1 else 0 end
									,case (@schema_option & 0x0000001000000000) when 0x0000001000000000 then 1 else 0 end
									,case (@schema_option & 0x0000008000000000) when 0x0000008000000000 then 1 else 0 end
									) +
		case when c.is_filestream = 1 and (@schema_option & 0x0000000120000000) = 0x0000000100000000 then
			end +
		case when c.is_sparse = 1 and (@schema_option & 0x0000020000000000)= 0x0000020000000000 then
				N' SPARSE'
			end +
		case c.is_identity when 1 then
				case (@schema_option & 0x0000000000000004) when 0x0000000000000004 then
					case c.is_nullable 	when 0 then N' NOT NULL'
								else N' NULL'
					end +
					N' IDENTITY (' + cast(ic.seed_value as nvarchar(128)) + N',' + cast(ic.increment_value as nvarchar(128)) + N') NOT FOR REPLICATION'
				--if we replicate without identity, can't set the column to 'NOT NULL'
				else N' NULL'
				case c.is_nullable 	when 0 then N' NOT NULL'
								else N' NULL'
			end +
		case t.EnumType when @subtype_addcolumn then
				ISNULL(N' CONSTRAINT ' + quotename(d.name) + N' DEFAULT ' + definition, N'') +
						case t.ColumnAttr collate database_default  when N'WITH VALUES' then N' WITH VALUES'
													else N''
				else N''
		,column_id = c.column_id
	from sys.columns c
		left outer join sys.default_constraints d
							on c.object_id = d.parent_object_id
							and c.column_id = d.parent_column_id
		left outer join sys.identity_columns ic
							on ic.object_id = c.object_id
							and ic.column_id = c.column_id
		join #tran_altertable t on c.name = t.ColumnName collate database_default
	where c.object_id = @objid and (t.EnumType & (@subtype_addcolumn | @subtype_altercolumn) > 0)
		and c.column_id not in (select column_id from sys.computed_columns where object_id = @objid)

	if @@ERROR<>0
		goto FAILURE	

	if @debug_print = 1
		select 'stage' = 'sp_MSfixup_single_artddls:exit'
					,'comments=' = 'now with add/alter cols'
					, * from #tran_altertable

	commit tran single_cmds
	return 0
	rollback tran single_cmds
	commit tran
	return 1

Last revision SQL2008SP2
See also

  sp_MSfixup_single_ddls (Procedure)
sp_MStran_altertable (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash