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_bindefault(nvarchar @defname, nvarchar @objname
, varchar @futureonly)
MetaData:
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
No comments:
Post a Comment