Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_unbindrule

  No additional text.


Syntax
create procedure sys.sp_unbindrule --- 1996/08/13 13:33
	@objname nvarchar(776),        -- table/column or datatype name
	@futureonly varchar(15) = NULL -- flag to indicate extent of binding
as
	declare @oldrule int		-- current rule for type
	declare @tabname sysname    -- name of table
	declare @colname sysname    -- name of column
	declare @futurevalue varchar(15)	-- the value of @futureonly that causes
										--  the binding to be limited

	declare
		@vc1			nvarchar(517)
	declare
		 @obj_id		int
		,@col_id		int
		,@rule			int
		,@xusertype		int
		,@schid			int

		,@owner_name	sysname
		,@obj_name		sysname

		,@UnqualObj			sysname
		,@QualObj1			sysname
		,@QualObj2			sysname
		,@QualObj3			sysname

	set cursor_close_on_commit	off

	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 @UnqualObj = parsename(@objname, 1),
			@QualObj1 = parsename(@objname, 2),
			@QualObj2 = parsename(@objname, 3),
			@QualObj3 = parsename(@objname, 4)

	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)
	begin
		raiserror(15084,-1,-1)
		return (1)
	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 unbound.
	if @QualObj1 is not null
	begin
		if (@QualObj2 is not null)
			select @vc1 = QuoteName(@QualObj2) + '.' + QuoteName(@QualObj1)
		else
			select @vc1 = QuoteName(@QualObj1)

		-- check if table and column exists
		select	@obj_id = c.object_id, @rule = c.rule_object_id
			from sys.columns c join sys.tables o
				on c.object_id = o.object_id
			where c.name = @UnqualObj
				and o.object_id = object_id(@vc1,'local')
		if @obj_id is not null
		begin

			if @rule = 0
			begin
				raiserror(15238,-1,-1,@objname)
				return (1)
			end

			BEGIN TRANSACTION

			-- Update schema count
			--  for the object in the sysobjects table.
			EXEC %%Object(MultiName = @vc1).LockMatchID(ID = @obj_id, Exclusive = 1, BindInternal = 0)

			-- check again if table and column exist
			if @@error = 0
				select	@col_id = column_id
					from sys.columns
					where object_id = @obj_id
						and name = @UnqualObj
			if @col_id is null
			begin
				COMMIT TRANSACTION
				raiserror(15148,-1,-1, @objname)
				return (1)
			end

			EXEC %%ColumnEx(ObjectID = @obj_id, Name = @UnqualObj).SetRule(ID = 0)
			-- EMDEventType(x_eet_Unbind_Rule), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
			-- -1 means ignore target, target major id, target minor id, target name,
			-- # of parameters, 5 parameters
			EXEC %%System().FireTrigger(ID = 243, ID = 1, ID = @obj_id, ID = 1, Value = @UnqualObj,
				ID = -1, ID = 0, ID = 0, Value = NULL, ID = 2,
				Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

			COMMIT TRANSACTION
			raiserror(15522,-1,-1)
		end
	end

	if @obj_id is null
	begin

		BEGIN TRANSACTION

		if @QualObj2 is null
			select @oldrule = rule_object_id, @xusertype = user_type_id,
					@schid = schema_id
				from sys.types
				where user_type_id = type_id(@objname)

		-- 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
			COMMIT TRANSACTION
			raiserror(15148,-1,-1,@objname)
			return (1)
		end

		if @oldrule = 0
		begin
			COMMIT TRANSACTION
			raiserror(15239,-1,-1,@UnqualObj)
			return (1)
		end

		EXEC %%ScalarType(ID = @xusertype).SetRule(ID = 0)

		raiserror(15523,-1,-1)

		select @futureonly = lower(@futureonly)
		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_unbindrule_1 cursor local static for
			select	distinct
					o.object_id, schema_name(o.schema_id), o.name, 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.rule_object_id = 0) or
						  (c.rule_object_id = @oldrule))
					order by o.object_id
					for read only

			open ms_crs_unbindrule_1

			fetch next from ms_crs_unbindrule_1 into
				@obj_id, @owner_name, @obj_name, @col_id

			while @@fetch_status = 0
			begin

				if @cur_tab_id <> @obj_id	-- not same table
				begin
					select @cur_tab_id = @obj_id
					select @vc1 = quotename(@owner_name) + '.' + quotename(@obj_name)

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

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

				fetch next from ms_crs_unbindrule_1 into
					@obj_id, @owner_name, @obj_name, @col_id

			end

			deallocate ms_crs_unbindrule_1
			raiserror(15524,-1,-1)
		end

		-- EMDEventType(x_eet_Unbind_Rule), EMDUniversalClass(x_eunc_Type), src major id, src minor id, src name
		-- -1 means ignore target, target major id, target minor id, target name,
		-- # of parameters, 5 parameters
		EXEC %%System().FireTrigger(ID = 243, ID = 6, ID = @xusertype, ID = 0, Value = NULL,
			ID = -1, ID = 0, ID = 0, Value = NULL, ID = 2,
			Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
		COMMIT TRANSACTION
	end

	return (0)	--sp_unbindrule

 
Last revision SQL2008SP2
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