The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_unbindrule(nvarchar @objname, varchar @futureonly)
MetaData:
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 = @col_id, 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
No comments:
Post a Comment