sp_tableoption (T-SQL)

Sets option values for user-defined tables.

Syntax

sp_tableoption [@TableNamePattern =] 'table'
    [,[@OptionName =] 'option_name']
    [,[@OptionValue =] 'value']

Arguments

[@TableNamePattern =] 'table'

Is the qualified or nonqualified name of a user-defined database table. If a fully qualified table name, including a database name, is provided, the database name must be the name of the current database. Table options for multiple tables can no longer be set at the same time. table_pattern is nvarchar(776), with no default.

[@OptionName =] 'option_name'

Is a table option name. option_name is varchar(35), with a default of NULL, which lists all available table options and the corresponding default values assigned to new tables (0 = false or off, 1 = true or on). option_name can have these values.

 

Value	Description
pintable	When disabled (the default), it marks the table as no longer RAM-resident. When enabled, marks the table as RAM-resident.
table lock on bulk load	When disabled (the default), it causes the bulk load process on user-defined tables to obtain row locks. When enabled, it causes the bulk load processes on user-defined tables to obtain a bulk update lock.
insert row lock	For Microsoft® SQL Server™ version 6.5, enabled or disabled Insert Row Locking (IRL) operations on the specified table. Row-level locking is enabled by default in SQL Server version 7.0. The locking strategy of SQL Server is row locking with possible promotion to page or table locking. This option does not alter the locking behavior of SQL Server (it has no effect) and is included only for compatibility of existing scripts and procedures. In a future version of SQL Server, this option may not be supported.

[@OptionValue =] 'value'

Is whether the option_name is enabled (true or on) or disabled (false or off). value is varchar(12), with a default of NULL, which lists the current settings for all matching tables.

Return Code Values

0 (success) or error number (failure)

Result Sets

Permissions

Only members of the sysadmin fixed server role can modify the pintable table option.

Members of the sysadmin fixed server role or the db_owner fixed database role can modify the insert row lock option for any user-defined table. Other users can modify options only for tables they own.

Any user can run sp_tableoption to generate a report listing table option values for all user-defined tables.

Examples

A. Display the available table options for the sales table and their default values

USE pubs

EXEC sp_tableoption 'sales'

B. Display the option value for the specified option

USE pubs

EXEC sp_tableoption 'sales', 'pintable'

C. Disable the pintable option for the sales table

USE pubs

EXEC sp_tableoption 'sales', 'pintable', 'false'

See Also