sp_tableoption

Sets option values for user-defined tables.

Syntax

sp_tableoption @TableNamePattern [, '@OptionName'] [, '@OptionValue']

where

@TableNamePattern

Specifies the qualified or non-qualified name of a user-defined database table. This parameter can be any pattern appropriate for the LIKE operator. Use single quotation marks (') if specifying @TableNamePattern with the LIKE operator. Quotation marks are not necessary if a single tablename is specified. If a fully qualified table name, including a database name, is provided the database name must be the name of the current database.

@OptionName

Specifies a table option name. The default is NULL, which lists all available table options and the corresponding default values assigned to new tables (0 = false, 1 = true). These are the possible values for @OptionName.

@OptionName	Default	Description
insert row lock	false	Disables (false) or enables (true) the insert row locking (IRL) feature for the specified table.
pintable	false	Marks the table either as either no longer RAM-resident (false) or as RAM-resident (true).

@OptionValue

Specifies whether @OptionName is enabled or disabled. The possible values are true or false. The default is NULL, which lists the current settings for all matching tables.

Examples

A.    Enable insert row lock Option for All Tables of the Current Database

EXECUTE sp_tableoption '%.%', 'insert row lock', 'true'
  

B.    Disable insert row lock Option for sales Table

EXECUTE sp_tableoption 'sales', 'insert row lock', 'false'
  

C.    Report on insert row locking Status for All Tables of the Current Database

EXECUTE sp_tableoption '%.%', 'insert row lock'
  

Permissions

Only the system administrator can modify the pintable table option.

The system administrator or database owner can modify the insert row lock option for any user-defined table. Users other than the system administrator or database owner 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.