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_bindrule(nvarchar @rulename, nvarchar @objname
, varchar @futureonly)
MetaData:
create procedure sys.sp_bindrule -- 1996/08/14 15:02 @rulename nvarchar(776), -- name of the rule @objname nvarchar(776), -- table or usertype name @futureonly varchar(15) = NULL -- column name as declare @ruleid int -- id of the rule 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 ,@colname sysname ,@oldrule int -- current rule for type ,@xtype smallint ,@xtypelen int ,@xusertype int ,@schid int declare @UnqualRule sysname ,@QualRule1 sysname ,@QualRule2 sysname ,@QualRule3 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 @UnqualRule = parsename(@rulename, 1), @QualRule1 = parsename(@rulename, 2), @QualRule2 = parsename(@rulename, 3), @QualRule3 = parsename(@rulename, 4) select @UnqualObj = parsename(@objname, 1), @QualObj1 = parsename(@objname, 2), @QualObj2 = parsename(@objname, 3), @QualObj3 = parsename(@objname, 4) if (@UnqualRule is null OR @QualRule3 is not null) begin raiserror(15253,-1,-1,@rulename) 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 (@QualRule2 is not null and @QualRule2 <> db_name())) begin raiserror(15077,-1,-1) return (1) end -- Check that the @futureonly argument, if supplied, is correct. if (@futureonly is not null) begin select @futureonly = lower(@futureonly) begin if (@futureonly <> @futurevalue) begin raiserror(15106,-1,-1) return (1) end end end BEGIN TRANSACTION -- Check to see that the rule exists and get its id. select @ruleid = object_id from sys.objects where object_id = object_id(@rulename,'local') and type='R ' -- rule object 7 -- Share Lock rule so that it cannot be dropped if not (@ruleid is null) begin EXEC %%Object(MultiName = @rulename).LockMatchID(ID = @ruleid, Exclusive = 0, BindInternal = 0) if @@error <> 0 select @ruleid = null end if @ruleid is null begin raiserror(15017,-1,-1,@rulename) 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 rule bound. 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 o.object_id = c.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 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, @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 image, text, or timestamp, disallow the bind. -- Rules can't be bound to image, text, or timestamp columns. -- The types are checked in case -- there is a user-defined datatype that is an image or text. -- User-defined datatypes mapping to timestamp are not allowed -- by sp_addtype. -- If the column is computed, disallow the bind. if ( type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp', 'xml') or (type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary') and @xtypelen = -1) or (ColumnProperty(object_id(@vc1,'local'), @UnqualObj, 'IsComputed') = 1) or @is_sparse = 1 or @is_column_set = 1 or @xtype = 240 ) -- CLR UDT begin raiserror(15107,-1,-1) goto error_abort_exit end EXEC %%ColumnEx(ObjectID = @tab_id, Name = @UnqualObj).SetRule(ID = @ruleid) -- EMDEventType(x_eet_Bind_Rule), 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 = 219, ID = 1, ID = @ruleid, ID = 0, Value = NULL, ID = 1, ID = @tab_id, ID = 1, Value = @UnqualObj, ID = 3, Value = @rulename, Value = @objname, Value = @futureonly, Value = NULL, Value = NULL, Value = NULL, Value = NULL) raiserror(15514,-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 rule for the datatype. if @QualObj2 is null select @oldrule = rule_object_id, @xtype = system_type_id, @xusertype = user_type_id, @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 the column type is image, text, or timestamp, disallow the bind. -- Rules can't be bound to image, text columns or columns of CLR UDT types if ( type_name(@xtype) in ('text', 'ntext', 'image', 'timestamp', 'xml') or (type_name(@xtype) in ('varchar', 'nvarchar', 'varbinary') and @xtypelen = -1) or @xtype = 240 ) -- CLR UDT begin raiserror(15107,-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).SetRule(ID = @ruleid) raiserror(15515,-1,-1) -- Now see if there are any columns with the usertype that 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_bindrule_1 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.rule_object_id = 0) or (c.rule_object_id = @oldrule)) order by c.object_id for read only open ms_crs_bindrule_1 fetch next from ms_crs_bindrule_1 into @tab_id, @col_id -- take care of rename 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 -- Update syscolumns with new binding. 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).SetRule(ID = @ruleid) end fetch next from ms_crs_bindrule_1 into @tab_id, @col_id end deallocate ms_crs_bindrule_1 raiserror(15516,-1,-1) end -- EMDEventType(x_eet_Bind_Rule), 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 = 219, ID = 1, ID = @ruleid, ID = 0, Value = NULL, ID = 6, ID = @xusertype, ID = 0, Value = NULL, ID = 3, Value = @rulename, 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_bindrule
No comments:
Post a Comment