Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
sys.tables (Transact-SQL)
Collapse the table of content
Expand the table of content

sys.tables (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a row for each user table in SQL Server.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.


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).

Applies to: SQL Server 2012 through SQL Server 2016.

durability

tinyint

The following are possible values:

0 = SCHEMA_AND_DATA

1 = SCHEMA_ONLY

The value of 0 is the default value.

Applies to: SQL Server 2014 through SQL Server 2016.

durability_desc

nvarchar(60)

The following are the possible values:

SCHEMA_AND_DATA

SCHEMA_ONLY

The value of SCHEMA_AND_DATA indicates that the table is a durable, in-memory table. SCHEMA_AND_DATA is the default value for memory optimized tables. The value of SCHEMA_ONLY indicates that the table data will not be persisted upon restart of the database with memory optimized objects.

Applies to: SQL Server 2014 through SQL Server 2016.

is_memory_optimized

bit

The following are the possible values:

0 = not memory optimized.

1 = is memory optimized.

A value of 0 is the default value.

Memory optimized tables are in-memory user tables, the schema of which is persisted on disk similar to other user tables. Memory optimized tables can be accessed from natively compiled stored procedures.

Applies to: SQL Server 2014 through SQL Server 2016.

temporal_type

tinyint

The numeric value representing the type of table:

0 = NON_TEMPORAL_TABLE

1 = HISTORY_TABLE

2 = SYSTEM_VERSIONED_TEMPORAL_TABLE

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

temporal_type_desc

nvarchar(60)

The text description of the type of table:

NON_TEMPORAL_TABLE

HISTORY_TABLE

SYSTEM_VERSIONED_TEMPORAL_TABLE

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

is_remote_data_archive_enabled

bit

Indicates whether the table is Stretch-enabled.

0 = The table is not Stretch-enabled.

1 = The table is Stretch-enabled.

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016

For more info, see Stretch Database.

remote_data_archive_migration_state

tinyint

Indicates the migration state of the table.

0 = DISABLED. The table is not Stretch-enabled.

1 = ENABLED_MIGRATION_OFF. The table is Stretch-enabled, but data migration is off.

2 = ENABLED_MIGRATION_ON. The table is Stretch-enabled, and data migration is on.

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016

remote_data_archive_migration_state_desc

nvarchar(60)

Contains the description for the value of remote_data_archive_migration_state.

DISABLED = 0. The table is not Stretch-enabled.

ENABLED_MIGRATION_OFF = 1. The table is Stretch-enabled, but data migration is off.

ENABLED_MIGRATION_ON = 2. The table is Stretch-enabled, and data migration is on.

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016

is_external

bit

TBD

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.

The following example returns all of the user tables that do not have a primary key.

SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name 
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;
GO

The following example shows how related temporal data can be exposed.

Applies to: SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016.

SELECT T1.object_id, T1.name as TemporalTableName, SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T2.name as HistoryTableName, SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T1.temporal_type_desc
FROM sys.tables T1
LEFT JOIN sys.tables T2 
ON T1.history_table_id = T2.object_id
ORDER BY T1.temporal_type desc

Community Additions

ADD
Show:
© 2015 Microsoft