June 11, 2012

sp_tableoption (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_tableoption(nvarchar @TableNamePattern
, varchar @OptionName
, varchar @OptionValue)

MetaData:

 create procedure sys.sp_tableoption  
@TableNamePattern nvarchar(776)
,@OptionName varchar(35)
,@OptionValue varchar(12)
as
-- declare AND INIT VARIABLES
declare @OPTbulklock varchar(25)
,@OPTtextinrow varchar(25)
,@OPTpintable varchar(25)
,@OPLargeValOutOfRow varchar(28)
,@OPUseVarDecimal varchar(25)
,@CurrentDBId int
,@ColId int
,@TabId int
,@opt_value int
,@opt_flag bit
,@permit bit -- locked after permission check?
,@ret int

select @OptionName = lower(@OptionName collate Latin1_General_CI_AS)
,@OPTbulklock = 'table lock on bulk load'
,@OPTtextinrow = 'text in row'
,@OPTpintable = 'pintable'
,@OPLargeValOutOfRow = 'large value types out of row'
,@OPUseVarDecimal = 'vardecimal storage format'
,@permit = 1
,@CurrentDBId = db_id()

-- DISALLOW USER TRANSACTION (except for in 'text in row' and 'large value types out of row' ) --
set nocount on
set implicit_transactions off
if (@@trancount > 0 AND @OptionName = @OPTbulklock)
begin
raiserror(15002,-1,-1,'sys.sp_tableoption')
return @@error
end

-- VALIDATE OPTION VALUE
select @opt_value =
case when (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) then 1
when (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) then 0
when (@OptionName = @OPTtextinrow AND ISNUMERIC (@OptionValue) <> 0)
then convert (int, @OptionValue)
end

-- ERROR if INVALID OPTION NAME OR VALUE
if @opt_value is null OR @OptionName is null OR
(@OptionName NOT IN (@OPTbulklock, @OPTtextinrow, @OPLargeValOutOfRow, @OPTpintable, @OPUseVarDecimal))
begin
raiserror(15600,-1,-1, 'sys.sp_tableoption')
return @@error
end

-- Return silently when option pintable is specifed. This functionality no longer exists.
if (@OptionName = @OPTpintable)
return 0

if (@OptionName = @OPTtextinrow)
begin
if (@opt_value != 0 and @opt_value != 1 and
(@opt_value < 24 or @opt_value > 7000))
begin -- Invalid value
raiserror (15112,-1,-1)
return @@error
end
end

-- vardecimal storage format is locked down in SQL Azure.
if (@OptionName = @OPUseVarDecimal and serverproperty('EngineEdition') = 5)
begin
raiserror(40512,-1,-1,@OptionName)
return @@error
end

BEGIN TRANSACTION

-- VERIFY WE HAVE A USER-TABLE BY THIS NAME IN THE DATABASE
select @TabId = object_id from sys.tables
where object_id = object_id(@TableNamePattern, 'local')

if not (@TabId is null)
begin
-- LOCK TABLE, CHECK STANDARD TABLE-DDL PERMISSIONS
EXEC %%Object(MultiName = @TableNamePattern).LockMatchID(ID = @TabId, Exclusive = 1, BindInternal = 0)
if @@error <> 0
select @permit = 0, @TabId = null
end

if @TabId is null -- Not found/permission deny
begin
COMMIT TRANSACTION
raiserror(15388,-1,-1,@TableNamePattern)
return @@error
end

-- HANDLE TEXT-IN-ROW option
if (@OptionName = @OPTtextinrow)
begin
-- invalidate inrow text pointer for the table
--
dbcc invalidate_textptr_objid(@TabId)
dbcc no_textptr(@TabId, @opt_value)
end

-- HANDLE TABLOCK-ON-BCP option
else if (@OptionName = @OPTbulklock)
begin
-- Make required change
if ObjectProperty(@TabId, 'TableIsLockedOnBulkLoad') <> @opt_value
begin
-- SetLockOnBulkLoad expect bit value
select @opt_flag = @opt_value
EXEC %%Relation(ID = @TabId).SetLockOnBulkLoad(Value = @opt_flag)
end
end
else if (@OptionName = @OPLargeValOutOfRow)
begin
-- SetLargeValuesTypeOutOfRow expects bit value
select @opt_flag = @opt_value
EXEC %%Relation(ID = @TabId).SetLargeValuesTypeOutOfRow(Value = @opt_flag)
select @ret = @@error
if @ret <> 0
begin
COMMIT TRANSACTION
return @ret
end
end
else if (@OptionName = @OPUseVarDecimal)
begin
-- SetUseVarDecimal expects bit value.
select @opt_flag = convert(bit, @opt_value)

-- No-op if the property is already in the desired state.
if ObjectProperty(@TabId, 'TableHasVarDecimalStorageFormat') <> @opt_flag
begin
exec %%Relation(ID = @TabId).SetUseVarDecimal(Value = @opt_flag)
select @ret = @@error
if @ret <> 0
begin
COMMIT TRANSACTION
return @ret
end
end
end

-- EMDEventType(x_eet_AlterTable), EMDUniversalClass( x_eunc_Table), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 22, ID = 1, ID = @TabId, ID = 0, Value = NULL,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 3, Value = @TableNamePattern, Value = @OptionName, Value = @OptionValue, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

COMMIT TRANSACTION

-- return success
return 0 -- sp_tableoption

No comments:

Post a Comment

Total Pageviews