Table of Contents

Class ObjectSchema.Table

Namespace
LinqToDB.Tools.DataProvider.SqlServer.Schemas
Assembly
linq2db.Tools.dll

sys.tables (Transact-SQL)

Applies to: √ SQL Server √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW) √ SQL analytics endpoint in Microsoft Fabric √ Warehouse in Microsoft Fabric

Returns a row for each user table in a SQL Server database.


See sys.tables.

[Table(Schema = "sys", Name = "tables", IsView = true)]
public class ObjectSchema.Table : ObjectSchema.Object
Inheritance
ObjectSchema.Table
Inherited Members
Extension Methods

Properties

DataRetentionPeriod

data_retention_period NULL int

The numeric value representing duration of the data retention period in units specified with data_retention_period_unit.

Applies to: Azure SQL Edge only 
[Column("data_retention_period")]
[Nullable]
public int? DataRetentionPeriod { get; set; }

Property Value

int?

DataRetentionPeriodUnit

data_retention_period_unit NULL int

The numeric value representing type of data retention period unit.

-1: INFINITE
0: SECOND
1: MINUTE
2: HOUR
3: DAY
4: WEEK
5: MONTH
6: YEAR

Applies to: Azure SQL Edge only 
[Column("data_retention_period_unit")]
[Nullable]
public int? DataRetentionPeriodUnit { get; set; }

Property Value

int?

DataRetentionPeriodUnitDesc

data_retention_period_unit_desc NULL nvarchar(10)

The text description of type of data retention period unit.

INFINITE
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
YEAR

Applies to: Azure SQL Edge only 
[Column("data_retention_period_unit_desc")]
[Nullable]
public string? DataRetentionPeriodUnitDesc { get; set; }

Property Value

string

Durability

durability NULL tinyint

The following are possible values:

0 = SCHEMA_AND_DATA
1 = SCHEMA_ONLY

A value of 0 is the default value.

Applies to: SQL Server 2014 (12.x) and later versions, and Azure SQL Database 
[Column("durability")]
[Nullable]
public byte? Durability { get; set; }

Property Value

byte?

DurabilityDesc

durability_desc NULL nvarchar(60)

The following are the possible values:

SCHEMA_ONLY
SCHEMA_AND_DATA

A 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. A value of SCHEMA_ONLY indicates that the table data isn't persisted upon restart of the database with memory optimized objects.

Applies to: SQL Server 2014 (12.x) and later versions, and Azure SQL Database 
[Column("durability_desc")]
[Nullable]
public string? DurabilityDesc { get; set; }

Property Value

string

FilestreamDataSpaceID

filestream_data_space_id NULL int

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) 
[Column("filestream_data_space_id")]
[Nullable]
public int? FilestreamDataSpaceID { get; set; }

Property Value

int?

HasReplicationFilter

has_replication_filter NULL bit

1 = Table has a replication filter. 
[Column("has_replication_filter")]
[Nullable]
public bool? HasReplicationFilter { get; set; }

Property Value

bool?

HasUncheckedAssemblyData

has_unchecked_assembly_data NOT NULL 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. 
[Column("has_unchecked_assembly_data")]
[NotNull]
public bool HasUncheckedAssemblyData { get; set; }

Property Value

bool

HistoryRetentionPeriod

history_retention_period NULL int

The numeric value representing duration of the temporal history retention period in units specified with history_retention_period_unit.

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database 
[Column("history_retention_period")]
[Nullable]
public int? HistoryRetentionPeriod { get; set; }

Property Value

int?

HistoryRetentionPeriodUnit

history_retention_period_unit NULL int

The numeric value representing type of temporal history retention period unit.

-1: INFINITE
0: SECOND
1: MINUTE
2: HOUR
3: DAY
4: WEEK
5: MONTH
6: YEAR

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database 
[Column("history_retention_period_unit")]
[Nullable]
public int? HistoryRetentionPeriodUnit { get; set; }

Property Value

int?

HistoryRetentionPeriodUnitDesc

history_retention_period_unit_desc NULL nvarchar(10)

The text description of type of temporal history retention period unit.

INFINITE
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
YEAR

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database 
[Column("history_retention_period_unit_desc")]
[Nullable]
public string? HistoryRetentionPeriodUnitDesc { get; set; }

Property Value

string

HistoryTableID

history_table_id NULL int

When temporal_type is 2 or ledger_type is 2, returns object_id of the table that maintains historical data for a temporal table, otherwise returns NULL.

Applies to: SQL Server 2016 (13.x) and later versions, and Azure SQL Database 
[Column("history_table_id")]
[Nullable]
public int? HistoryTableID { get; set; }

Property Value

int?

IsDroppedLedgerTable

is_dropped_ledger_table NULL bit

Indicates a ledger table that was dropped.

Applies to: SQL Server 2022 (16.x) and later versions, and Azure SQL Database 
[Column("is_dropped_ledger_table")]
[Nullable]
public bool? IsDroppedLedgerTable { get; set; }

Property Value

bool?

IsEdge

is_edge NULL bit

1 = Graph edge table.
0 = Not a graph edge table.

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database 
[Column("is_edge")]
[Nullable]
public bool? IsEdge { get; set; }

Property Value

bool?

IsExternal

is_external NOT NULL bit

Indicates table is an external table.

0 = The table isn't an external table.
1 = The table is an external table.

Applies to: SQL Server 2016 (13.x) and later versions, Azure SQL Database, and Azure Synapse Analytics 
[Column("is_external")]
[NotNull]
public bool IsExternal { get; set; }

Property Value

bool

IsFileTable

is_filetable NULL bit

1 = Table is a FileTable.

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

Applies to: SQL Server 2012 (11.x) and later versions, and Azure SQL Database 
[Column("is_filetable")]
[Nullable]
public bool? IsFileTable { get; set; }

Property Value

bool?

IsMemoryOptimized

is_memory_optimized NULL 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 (12.x) and later versions, and Azure SQL Database. 
[Column("is_memory_optimized")]
[Nullable]
public bool? IsMemoryOptimized { get; set; }

Property Value

bool?

IsMergePublished

is_merge_published NULL bit

1 = Table is published using merge replication. 
[Column("is_merge_published")]
[Nullable]
public bool? IsMergePublished { get; set; }

Property Value

bool?

IsNode

is_node NULL bit

1 = Graph node table.
0 = Not a graph node table.

Applies to: SQL Server 2017 (14.x) and later versions, and Azure SQL Database 
[Column("is_node")]
[Nullable]
public bool? IsNode { get; set; }

Property Value

bool?

IsRemoteDataArchiveEnabled

is_remote_data_archive_enabled NULL bit

Indicates whether the table is Stretch-enabled.

0 = The table isn't Stretch-enabled.
1 = The table is Stretch-enabled.

For more info, see [Stretch Database](/previous-versions/sql/sql-server/stretch-database/stretch-database).

Applies to: SQL Server 2016 (13.x) and later versions, and Azure SQL Database 
[Column("is_remote_data_archive_enabled")]
[Nullable]
public bool? IsRemoteDataArchiveEnabled { get; set; }

Property Value

bool?

IsReplicated

is_replicated NULL bit

1 = Table is published using snapshot replication or transactional replication. 
[Column("is_replicated")]
[Nullable]
public bool? IsReplicated { get; set; }

Property Value

bool?

IsSyncTranSubscribed

is_sync_tran_subscribed NULL bit

1 = Table is subscribed using an immediate updating subscription. 
[Column("is_sync_tran_subscribed")]
[Nullable]
public bool? IsSyncTranSubscribed { get; set; }

Property Value

bool?

IsTrackedByCdc

is_tracked_by_cdc NULL bit

1 = Table is enabled for change data capture. For more information, see sys.sp_cdc_enable_table (Transact-SQL). 
[Column("is_tracked_by_cdc")]
[Nullable]
public bool? IsTrackedByCdc { get; set; }

Property Value

bool?

LargeValueTypesOutOfRow

large_value_types_out_of_row NULL bit

1 = Large value types are stored out-of-row. For more information, see sp_tableoption (Transact-SQL). 
[Column("large_value_types_out_of_row")]
[Nullable]
public bool? LargeValueTypesOutOfRow { get; set; }

Property Value

bool?

LedgerType

ledger_type NULL tinyint

The numeric value indicates if the table is a ledger table.

0 = NON_LEDGER_TABLE
1 = HISTORY_TABLE (associated with an updatable ledger table)
2 = UPDATABLE_LEDGER_TABLE
3 = APPEND_ONLY_LEDGER_TABLE

For more information on database ledger, see Ledger.

Applies to: SQL Server 2022 (16.x) and later versions, and Azure SQL Database 
[Column("ledger_type")]
[Nullable]
public byte? LedgerType { get; set; }

Property Value

byte?

LedgerTypeDesc

ledger_type_desc NULL nvarchar(60)

The text description of a value in the ledger_type column:

NON_LEDGER_TABLE
HISTORY_TABLE
UPDATABLE_LEDGER_TABLE
APPEND_ONLY_LEDGER_TABLE

Applies to: SQL Server 2022 (16.x) and later versions, and Azure SQL Database 
[Column("ledger_type_desc")]
[Nullable]
public string? LedgerTypeDesc { get; set; }

Property Value

string

LedgerViewID

ledger_view_id NULL int

When ledger_type IN (2, 3) returns object_id of the ledger view, otherwise returns NULL.

Applies to: SQL Server 2022 (16.x) and later versions, and Azure SQL Database 
[Column("ledger_view_id")]
[Nullable]
public int? LedgerViewID { get; set; }

Property Value

int?

LobDataSpaceID

lob_data_space_id NOT NULL 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 doesn't have LOB data. 
[Column("lob_data_space_id")]
[NotNull]
public int LobDataSpaceID { get; set; }

Property Value

int

LockEscalation

lock_escalation NULL tinyint

A value of the LOCK_ESCALATION option for the table:

0 = TABLE
1 = DISABLE
2 = AUTO 
[Column("lock_escalation")]
[Nullable]
public byte? LockEscalation { get; set; }

Property Value

byte?

LockEscalationDesc

lock_escalation_desc NULL nvarchar(60)

A text description of the lock_escalation option for the table. Possible values are: TABLE, AUTO, and DISABLE. 
[Column("lock_escalation_desc")]
[Nullable]
public string? LockEscalationDesc { get; set; }

Property Value

string

LockOnBulkLoad

lock_on_bulk_load NOT NULL bit

Table is locked on bulk load. For more information, see sp_tableoption (Transact-SQL). 
[Column("lock_on_bulk_load")]
[NotNull]
public bool LockOnBulkLoad { get; set; }

Property Value

bool

MaxColumnIDUsed

max_column_id_used NOT NULL int

Maximum column ID ever used by this table. 
[Column("max_column_id_used")]
[NotNull]
public int MaxColumnIDUsed { get; set; }

Property Value

int

TemporalType

temporal_type NULL tinyint

The numeric value representing the type of table:

0 = NON_TEMPORAL_TABLE
1 = HISTORY_TABLE (associated with a temporal table)
2 = SYSTEM_VERSIONED_TEMPORAL_TABLE

Applies to: SQL Server 2016 (13.x) and later versions, and Azure SQL Database 
[Column("temporal_type")]
[Nullable]
public byte? TemporalType { get; set; }

Property Value

byte?

TemporalTypeDesc

temporal_type_desc NULL nvarchar(60)

The text description of the type of table:

NON_TEMPORAL_TABLE
HISTORY_TABLE
SYSTEM_VERSIONED_TEMPORAL_TABLE

Applies to: SQL Server 2016 (13.x) and later versions, and Azure SQL Database 
[Column("temporal_type_desc")]
[Nullable]
public string? TemporalTypeDesc { get; set; }

Property Value

string

TextInRowLimit

text_in_row_limit NULL int

The maximum bytes allowed for text in row.

0 = Text in row option isn't set. For more information, see sp_tableoption (Transact-SQL). 
[Column("text_in_row_limit")]
[Nullable]
public int? TextInRowLimit { get; set; }

Property Value

int?

UsesAnsiNulls

uses_ansi_nulls NULL bit

Table was created with the SET ANSI_NULLS database option ON. 
[Column("uses_ansi_nulls")]
[Nullable]
public bool? UsesAnsiNulls { get; set; }

Property Value

bool?