April 16, 2012

sp_bindefault (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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

Total Pageviews