Dela via

sys.tables (Transact-SQL)

  • Artikel

Returns a row for each table object, currently only with sys.objects.type = U.

Column name

Data type

Description

<inherited columns>

 

For a list of columns that this view inherits, see sys.objects (Transact-SQL)

lob_data_space_id

int

A nonzero value is the ID of the data space (filegroup or partition scheme) that holds the large object binary (LOB) data for this table. Examples of LOB data types include varbinary(max), varchar(max), geography, or xml.

0 = The table does not LOB data.

filestream_data_space_id

int

Is the data space ID for a FILESTREAM filegroup or a partition scheme that consists of FILESTREAM filegroups.

To report the name of a FILESTREAM filegroup, execute the query SELECT FILEGROUP_NAME (filestream_data_space_id) FROM sys.tables.

sys.tables can be joined to the following views on filestream_data_space_id = data_space_id.

  • sys.filegroups

  • sys.partition_schemes

  • sys.indexes

  • sys.allocation_units

  • sys.fulltext_catalogs

  • sys.data_spaces

  • sys.destination_data_spaces

  • sys.master_files

  • sys.database_files

  • backupfilegroup (join on filegroup_id)

max_column_id_used

int

Maximum column ID ever used by this table.

lock_on_bulk_load

bit

Table is locked on bulk load. For more information, see sp_tableoption (Transact-SQL).

uses_ansi_nulls

bit

Table was created with the SET ANSI_NULLS database option ON.

is_replicated

bit

1 = Table is published using snapshot replication or transactional replication.

has_replication_filter

bit

1 = Table has a replication filter.

is_merge_published

bit

1 = Table is published using merge replication.

is_sync_tran_subscribed

bit

1 = Table is subscribed using an immediate updating subscription.

has_unchecked_assembly_data

bit

1 = Table contains persisted data that depends on an assembly whose definition changed during the last ALTER ASSEMBLY. Will be reset to 0 after the next successful DBCC CHECKDB or DBCC CHECKTABLE.

text_in_row_limit

int

The maximum bytes allowed for text in row.

0 = Text in row option is not set. For more information, see sp_tableoption (Transact-SQL).

large_value_types_out_of_row

bit

1 = Large value types are stored out-of-row. For more information, see sp_tableoption (Transact-SQL).

is_tracked_by_cdc

bit

1 = Table is enabled for change data capture. For more information, see sys.sp_cdc_enable_table (Transact-SQL).

lock_escalation

tinyint

The value of the LOCK_ESCALATION option for the table:

0 = TABLE

1 = DISABLE

2 = AUTO

lock_escalation_desc

nvarchar(60)

A text description of the lock_escalation option for the table. Possible values are: TABLE, AUTO, and DISABLE.

is_filetable

bit

1 = Table is a FileTable.

For more information about FileTables, see FileTables (SQL Server).

Does not apply to SQL Azure.

Permissions

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

See Also

Reference

Object Catalog Views (Transact-SQL)

Catalog Views (Transact-SQL)

DBCC CHECKDB (Transact-SQL)

DBCC CHECKTABLE (Transact-SQL)

Concepts

Querying the SQL Server System Catalog FAQ