Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSfixup_defaults_name

  No additional text.


Syntax

-- Name: sp_MSfixup_defaults_name

-- Owner: QunGuo

-- Description: this is a helper procedure which is used by ddl trigger to
--		handle the condition where user did not provide explicit default
--		name, by retrieving default name from system catelog view
--		it works off  prepopulated #tran_altertable table

--	add default is different from the rest of constraints because 'with values'
--	is not persisted in system catelog view, we need to depend

--add default can have any combination of the following three varations:
--1. in column definition, or adding to existing column
--		e.g. alter table foo add bar int default 5
--				alter table foo add bar int,  default 5 for foo_bar
--2. with or without explicit name
--		e.g. alter table foo add bar int constraint df_bar default 5
--3. with or without 'with values' clause
--		e.g. alter table foo add bar default 5 with values

--the goal is to reform the resultset from parser so that
--1. if 'WITH VALUES' default was added in the same DDL as base column, 'addcolumn' row need to reflect this
--		e.g. 	alter table foo add bar int default 5 with values
--			sys.sp_replddlparser returns:
--			ADDCOLUMN		foo	bar	
--			ADDDEFAULT		foo					WITH VALUES

--			alter table foo add bar int constraint df_bar default 5 with values
--			sys.sp_replddlparser returns:
--			ADDCOLUMN		foo	bar	
--			ADDDEFAULT		foo	df_bar		WITH VALUES

--			alter table foo add bar int, default 5 for bar with values
--			sys.sp_replddlparser returns:
--			ADDCOLUMN		foo	bar	
--			ADDCOLUMN		foo		
--			ADDDEFAULT		foo	df_bar		WITH VALUES

--			alter table foo add bar int, constraint df_bar default 5 for bar with values
--			sys.sp_replddlparser returns:
--			ADDCOLUMN		foo	bar	
--			ADDDEFAULT		foo	df_bar		WITH VALUES

--	in these cases parser resultset will either use base column name or explicit default name in adddefault row,
--	1.1. base column means user did not specify explicit default name, if base column exists in DDL, update the row
--				notice the middle row in the third example with empty df name, basically we are returning two rows for add default
--				in this particular case, the bottom row uses colname since default name was not specified, the middle row is redundent
--				but tells merge that there is a default without explicit name so they can reject, once merge implement similar
--				logic to handle no-name defaults, this redundent row and related logic should go away
--	1.2. explicit default name in ColumnName, use it to find base column name, if base column exists in DDL, update the row
--			
--2. if default is added to existing column, ColumnName need to reflect the real default name, regardless of 'WITH VALUES' setting
--		cuz sp_MSfixup_single_add will join with catelog view base on ColumnName
--		e.g. 	alter table foo add default 5 for bar
--			alter table foo add constraint df_bar default 5 for bar
--	in these cases parser resultset will have NULL or base column name as ColumnName
--	2.1. Null ColumnName, previous addcolumn should be the base, find default name by base column, update ColumnName
--	2.2. base column name, find default name by base column, update ColumnName


-- Parameter:

-- Returns: 1 or 0   0 = success

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

create procedure sys.sp_MSfixup_defaults_name
(
	@subtype_adddf int
	,@subtype_addcolumn int
	,@objid int
    ,@debug_print bit = 0
)
as
begin
		DECLARE @basecol nvarchar(512)
						,@defaultname nvarchar(512)
						,@dfname nvarchar(512)
						,@colattr sysname
						,@typeid nvarchar(32)
						,@modified_date datetime
						,@row_id int
						,@type_mask int
						,@done_mask int

		begin tran replddl_fixup_defaults_name
		save tran replddl_fixup_defaults_name

		DECLARE #fkcursor CURSOR LOCAL FAST_FORWARD for
			select id, ColumnName, ColumnAttr from #tran_altertable
				where EnumType = @subtype_adddf
					and (len(isnull(ColumnName, '')) = 0 or
						ColumnName collate database_default in (select name from sys.columns where object_id = @objid) or
						ColumnAttr = N'WITH VALUES' collate database_default)
				order by id desc
		open #fkcursor
		fetch #fkcursor into @row_id, @dfname, @colattr
		while @@fetch_status <> -1
		begin
			if @debug_print = 1
				select 'stage' = 'sp_MSfixup_defaults_name:query'
										,'@row_id' = @row_id
										,'@dfname' = @dfname
										,'@colattr' = @colattr
			set @basecol = NULL
			set @defaultname = NULL
			
			--first of all find base column name and default constraint name for current row
			
			--if ColumnName is the default name, find base col name so we can update addcolumn row if any
			if exists(select * from sys.default_constraints dc where dc.parent_object_id = @objid and dc.name = @dfname)
			begin
				set @defaultname = @dfname
				select @basecol = c.name 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
						where dc.parent_object_id = @objid and dc.name = @defaultname
			end
			else --ColumnName is not default constraint name, it's either base column name or NULL
			begin
				--if base column name, find default constraint name, we may need to update the row later
				if exists(select * from sys.columns c where c.object_id = @objid and c.name = @dfname)
				begin
					set @basecol = @dfname
					--this deletes the redundant adddefault row with empty ColumnName
					delete from #tran_altertable where id = @row_id - 1 and
												len(isnull(ColumnName, '')) = 0 and
												EnumType = @subtype_adddf
					if @@ERROR > 0
						goto FAILURE
				end
				else -- ColumnName is null
				begin
					--if @colname is null, default was added without explicit name and without 'for column',
					--which means this can not be adding to existing column, which means base column has to be added
					--in this very same DDL, find base col name by searching backward
					select top 1 @basecol = ColumnName from #tran_altertable where id < @row_id and
							EnumType = @subtype_addcolumn order by id desc
				end
				select @defaultname = dc.name 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
						where dc.parent_object_id = @objid and c.name = @basecol
			end	
			if @debug_print = 1
			select 'stage' = 'sp_MSfixup_defaults_name:setname'
									,'@defaultname' = @defaultname
									,'@basecol' = @basecol

			if @colattr = N'WITH VALUES'
			begin
				--this affects 1 row or 0 row depending on whether base column is in the same DDL or not
				update #tran_altertable set ColumnAttr = N'WITH VALUES'
						where ColumnName = @basecol and EnumType = @subtype_addcolumn
				if @@ERROR > 0
					goto FAILURE
			end

			--if @dfname is null, default was added without explicit name and without 'for column',
			--which means this can not be adding to existing column, that way we can safely delete it here since addcolumn row will take care of it in #single_ddl
			if  len(isnull(@dfname, '')) = 0
			begin
				delete from #tran_altertable where id = @row_id
				if @@ERROR > 0
					goto FAILURE
			end
			else
			begin
				--update ColumnName to default name
				update #tran_altertable set ColumnName = @defaultname where id = @row_id
				if @@ERROR > 0
					goto FAILURE
			end
			fetch #fkcursor into @row_id, @dfname, @colattr
		end
		close #fkcursor
		deallocate #fkcursor
		commit tran replddl_fixup_defaults_name
		return 0
FAILURE:
	close #fkcursor
	deallocate #fkcursor
	rollback tran replddl_fixup_defaults_name
	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