April 16, 2012

sp_bindrule (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_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

Total Pageviews