Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_bindefault

  No additional text.


Syntax
create procedure sys.sp_bindefault	--- 1996/08/30 20:04
	@defname nvarchar(776),			-- name of the default
	@objname nvarchar(776),			-- table or usertype name
	@futureonly varchar(15) = NULL	-- flag to indicate extent of binding
as
	declare @defid int					-- id of the default to bind
	declare @futurevalue varchar(15)	-- the value of @futureonly that causes
										--  the binding to be limited
	declare
		@vc1			nvarchar(517)
		,@tab_id		int
		,@col_id		int
		,@parent_obj	int
		,@colname		sysname
		,@xtype			tinyint
		,@xusertype		int
		,@xtypelen		int
		,@cur_default	int
		,@schid			int

	declare
		@UnqualDef			sysname
		,@QualDef1			sysname
		,@QualDef2			sysname
		,@QualDef3			sysname
		,@UnqualObj			sysname
		,@QualObj1			sysname
		,@QualObj2			sysname
		,@QualObj3			sysname

	set cursor_close_on_commit	off
	set nocount			on

	select @futurevalue = 'futureonly'	-- initialize @futurevalue

	-- When a default or rule is bound to a user-defined datatype, it is also
	--  bound, by default, to any columns of the user datatype that are currently
	--  using the existing default or rule as their default or rule.  This default
	--  action may be overridden by setting @futureonly = @futurevalue when the
	--  procedure is invoked.  In this case existing columns with the user
	--  datatype won't have their existing default or rule changed.

	-- get name parts 
	select @UnqualDef = parsename(@defname, 1),
			@QualDef1 = parsename(@defname, 2),
			@QualDef2 = parsename(@defname, 3),
			@QualDef3 = parsename(@defname, 4)

	select @UnqualObj = parsename(@objname, 1),
			@QualObj1 = parsename(@objname, 2),
			@QualObj2 = parsename(@objname, 3),
			@QualObj3 = parsename(@objname, 4)

	if (@UnqualDef is null OR @QualDef3 is not null)
	begin
		raiserror(15253,-1,-1,@defname)
		return (1)
	end

	if (@UnqualObj is null OR @QualObj3 is not null)
	begin
		raiserror(15253,-1,-1,@objname)
		return (1)
	end

	------------------  Verify database.
	if ((@QualObj2 is not null and @QualObj1 is null)
		or (@QualDef2 is not null and @QualDef2 <> db_name()))
	begin
		raiserror(15076,-1,-1)
		return (1)
	end

	-- Check that the @futureonly argument, if supplied, is correct.
	if (@futureonly is not null)
	begin
		select @futureonly = lower(@futureonly)
		if (@futureonly <> @futurevalue)
		begin
			raiserror(15100,-1,-1)
			return (1)
		end
	end

	BEGIN TRANSACTION

	-- Check to see that the default exists and get its id.
	select @defid = object_id, @parent_obj = parent_object_id from sys.objects
			where object_id = object_id(@defname, 'local')
				and type='D '	-- default object 6

	-- Share lock default so it cannot be dropped
	if not (@defid is null)
	begin
		EXEC %%Object(MultiName = @defname).LockMatchID(ID = @defid, Exclusive = 0, BindInternal = 0)
		if @@error <> 0
			select @defid = null
	end

	if @defid is null
	begin
		raiserror(15016,-1,-1,@UnqualDef)
		goto error_abort_exit
	end

	if @parent_obj > 0
	begin
		raiserror(15050,-1,-1,@defname)
		goto error_abort_exit
	end

	-- Try to resolve column first. We need to extract
	--  and verify the table and column names and make sure the user owns
	--  the table that is getting the default bound. We also need to ensure
	--  that we don't overwrite any DRI style defaults.
	if @QualObj1 is not null
	begin
		if (@QualObj2 is not null)
			select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
		else
			select @vc1 = QuoteName(@QualObj1)

		-- Check that table and column exist
		select	@tab_id = o.object_id
			from sys.tables o join sys.columns c
				on c.object_id = o.object_id
			where o.object_id = object_id(@vc1,'local')
				and c.name = @UnqualObj
				
 		if @tab_id is not null
		begin
			declare @is_sparse int
			declare @is_column_set int

			-- Since binding a default is a schema change, update schema count
			--  for the object in the sysobjects table.
			EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)

			-- Check again that table and column exist
			if @@error = 0
				select	@xtype = system_type_id,
						@xtypelen = max_length,
						@cur_default = default_object_id,
						@is_sparse = is_sparse,
						@is_column_set = is_column_set
					from sys.columns
					where object_id = @tab_id
						and name = @UnqualObj
			if @xtype is null
			begin
				raiserror(15148,-1,-1, @objname)
				goto error_abort_exit
			end

			-- If the column type is timestamp, varchar(max), nvarchar(max), varbinary(max), disallow the bind.
			-- If the column is computed, disallow the bind.
			if (   type_name(@xtype) in ('timestamp', 'xml')
				or ( type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary')
					  and @xtypelen = -1 )
				or ColumnProperty(@tab_id, @UnqualObj, 'IsComputed') = 1
				or @is_sparse = 1
				or @is_column_set = 1
				or @xtype = 240 ) -- CLR UDT
			begin
				raiserror(15101,-1,-1)
				goto error_abort_exit
			end

			-- If the column category is identity, disallow the bind.
			--  Defaults can't be bound to identity columns.
			if 1 = ColumnProperty(@tab_id, @UnqualObj, 'IsIdentity')
			begin
				raiserror(15102,-1,-1)
				goto error_abort_exit
			end

			-- Check to see if the column was created with or altered
			--  to have a DRI style default value.
			if (@cur_default is not null) and exists
					(select	*
						from	sys.objects o
						where	@cur_default     = o.object_id
						and		@tab_id            = o.parent_object_id)
			begin
				raiserror(15103,-1,-1)
				goto error_abort_exit
			end

			EXEC %%ColumnEx(ObjectID = @tab_id, Name = @UnqualObj).SetDefault(ID = @defid)
			
			-- EMDEventType(x_eet_Bind_Default), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
			-- EMDUniversalClass(x_eunc_Table), target major id, 1 means target name is column, target name,
			-- # of parameters, 5 parameters
			EXEC %%System().FireTrigger(ID = 218, ID = 1, ID = @defid, ID = 0, Value = NULL,
				ID = 1, ID = @tab_id, ID = 1, Value = @UnqualObj, ID = 3,
				Value = @defname, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
			raiserror(15511,-1,-1)
		end
	end

	-- We're binding to a user type.  In this case, the @objname
	--  is really the name of the user datatype.
	--  When we bind to a user type, any existing columns get changed
	--  to the new binding unless their current binding is not equal
	--  to the current binding for the usertype or if they set the
	--  @futureonly parameter to @futurevalue.
	if @tab_id is null
	begin

		--  Get the current default for the datatype.
		if @QualObj2 is null
			select @xusertype = user_type_id, @cur_default = default_object_id,
					@xtype = system_type_id,
					@xtypelen = max_length,
					@schid = schema_id
					from sys.types
					where user_type_id = type_id(@objname)
					and is_table_type=0

		-- Ex-lock and check permission
		if not (@xusertype is null)
		begin
			EXEC %%ScalarType(MultiName = @objname).LockMatchID(ID = @xusertype, Exclusive = 1)
			if (@@error <> 0)
				select @xusertype = null
		end
		
		if @xusertype is null
		begin
			raiserror(15148,-1,-1, @objname)
			goto error_abort_exit
		end
			
		if ((type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary') and
				 @xtypelen = -1)
			or type_name(@xtype) = 'xml'
			or @xtype = 240 ) -- CLR UDT
		begin
			raiserror(15101,-1,-1)
			goto error_abort_exit
		end

		if exists (select * from sys.columns c where user_type_id = @xusertype and c.is_sparse = 1)
		begin
			raiserror(33079,-1,-1, @objname)
			goto error_abort_exit
		end

		EXEC %%ScalarType(ID = @xusertype).SetDefault(ID = @defid)
		raiserror(15512,-1,-1)

		-- need the new binding.
		if isnull(@futureonly, ' ') <> @futurevalue
		begin

			declare	@cur_tab_id		int
					,@bad_tab_id	int

			select	@cur_tab_id = 0		-- detect table id change for lock schema
					,@bad_tab_id = 0	-- skip bad tables (dropped, etc)

			declare ms_crs_t1 cursor local static for
				select distinct
						c.object_id, c.column_id
				from	 sys.columns c join sys.tables o
					on c.object_id = o.object_id
				where c.user_type_id = @xusertype
				and	((c.default_object_id = 0)
					 or (c.default_object_id = @cur_default))
				order by c.object_id
						for read only

			open ms_crs_t1
			fetch next from ms_crs_t1 into
					@tab_id, @col_id

			while @@fetch_status = 0
			begin
				if @cur_tab_id <> @tab_id	-- not same table
				begin
					select @cur_tab_id = @tab_id
					select @vc1 = quotename(schema_name(OBJECTPROPERTY(@tab_id,'SchemaId'))) + '.'
								+ quotename(object_name(@tab_id))

					EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @tab_id, Exclusive = 1, BindInternal = 0)
					if @@error <> 0			-- bad table, eg. removed
						select @bad_tab_id = @tab_id
				end

				if @bad_tab_id <> @tab_id	-- table schema locked
				begin
					-- Column cannot be dropped due to type shared lock
					select @colname = COL_NAME(@tab_id, @col_id)
					EXEC %%ColumnEx(ObjectID = @tab_id, Name = @colname).SetDefault(ID = @defid)
				end

				fetch next from ms_crs_t1 into
						@tab_id, @col_id

			end

			deallocate ms_crs_t1
			raiserror(15513,-1,-1)
		end

		-- EMDEventType(x_eet_Bind_Default), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
		-- EMDUniversalClass(x_eunc_Type), target major id, target minor id, target name,
		-- # of parameters, 5 parameters
		EXEC %%System().FireTrigger(ID = 218, ID = 1, ID = @defid, ID = 0, Value = NULL,
			ID = 6, ID = @xusertype, ID = 0, Value = NULL, ID = 3,
			Value = @defname, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
	end

	-- SUCCESS 
	COMMIT TRANSACTION
	return (0)

error_abort_exit:
	COMMIT TRANSACTION
	return 1	-- sp_bindefault

 
Last revision 2008RTM
See also

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