Table of Contents

Class ObjectSchema.Table

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

sys.tables (Transact-SQL)

Applies to: √ SQL Server (all supported versions) √ Azure SQL Database √ Azure SQL Managed Instance √ Azure Synapse Analytics √ Analytics Platform System (PDW)

Returns a row for each user table in SQL Server.


See sys.tables.

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

Properties

CreateDate

Date the object was created.

[Column("create_date")]
[NotNull]
public DateTime CreateDate { get; set; }

Property Value

DateTime

Durability

Applies to: SQL Server 2014 (12.x) and later and Azure SQL Database.

The following are possible values:

0 = SCHEMA_AND_DATA

1 = SCHEMA_ONLY

The value of 0 is the default value.

[Column("durability")]
[Nullable]
public byte? Durability { get; set; }

Property Value

byte?

DurabilityDesc

Applies to: SQL Server 2014 (12.x) and later and Azure SQL Database.

The following are the possible values:

SCHEMA_ONLY

SCHEMA_AND_DATA

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.

[Column("durability_desc")]
[Nullable]
public string? DurabilityDesc { get; set; }

Property Value

string

FilestreamDataSpaceID

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)

[Column("filestream_data_space_id")]
[Nullable]
public int? FilestreamDataSpaceID { get; set; }

Property Value

int?

HasReplicationFilter

1 = Table has a replication filter.

[Column("has_replication_filter")]
[Nullable]
public bool? HasReplicationFilter { get; set; }

Property Value

bool?

HasUncheckedAssemblyData

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

Applies to: Azure SQL Database.

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

[Column("history_retention_period")]
[Nullable]
public int? HistoryRetentionPeriod { get; set; }

Property Value

int?

HistoryRetentionPeriodUnit

Applies to: Azure SQL Database.

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

-1: INFINITE

3: DAY

4: WEEK

5: MONTH

6: YEAR

[Column("history_retention_period_unit")]
[Nullable]
public int? HistoryRetentionPeriodUnit { get; set; }

Property Value

int?

HistoryRetentionPeriodUnitDesc

Applies to: Azure SQL Database.

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

INFINITE

DAY

WEEK

MONTH

YEAR

[Column("history_retention_period_unit_desc")]
[Nullable]
public string? HistoryRetentionPeriodUnitDesc { get; set; }

Property Value

string

HistoryTableID

Applies to: SQL Server 2016 (13.x) and later and Azure SQL Database.

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

[Column("history_table_id")]
[Nullable]
public int? HistoryTableID { get; set; }

Property Value

int?

IsDroppedLedgerTable

Applies to: Azure SQL Database.

Indicates a ledger table that has been dropped.

[Column("is_dropped_ledger_table")]
[NotNull]
public bool IsDroppedLedgerTable { get; set; }

Property Value

bool

IsEdge

Applies to: Azure SQL Database.

1 = This is a graph Edge table.

0 = This is not a graph Edge table.

[Column("is_edge")]
[Nullable]
public bool? IsEdge { get; set; }

Property Value

bool?

IsExternal

Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, and Azure Synapse Analytics.

Indicates table is an external table.

0 = The table is not an external table.

1 = The table is an external table.

[Column("is_external")]
[NotNull]
public bool IsExternal { get; set; }

Property Value

bool

IsFileTable

Applies to: SQL Server 2012 (11.x) and later and Azure SQL Database.

1 = Table is a FileTable.

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

[Column("is_filetable")]
[Nullable]
public bool? IsFileTable { get; set; }

Property Value

bool?

IsMSShipped

Object is created by an internal SQL Server component.

[Column("is_ms_shipped")]
[NotNull]
public bool IsMSShipped { get; set; }

Property Value

bool

IsMemoryOptimized

Applies to: SQL Server 2014 (12.x) and later and Azure SQL Database.

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.

[Column("is_memory_optimized")]
[Nullable]
public bool? IsMemoryOptimized { get; set; }

Property Value

bool?

IsMergePublished

1 = Table is published using merge replication.

[Column("is_merge_published")]
[Nullable]
public bool? IsMergePublished { get; set; }

Property Value

bool?

IsNode

Applies to: SQL Server 2017 (14.x) and Azure SQL Database.

1 = This is a graph Node table.

0 = This is not a graph Node table.

[Column("is_node")]
[Nullable]
public bool? IsNode { get; set; }

Property Value

bool?

IsPublished

Object is published.

[Column("is_published")]
[NotNull]
public bool IsPublished { get; set; }

Property Value

bool

IsRemoteDataArchiveEnabled

Applies to: SQL Server 2016 (13.x) and later and Azure SQL Database

Indicates whether the table is Stretch-enabled.

0 = The table is not Stretch-enabled.

1 = The table is Stretch-enabled.

For more info, see Stretch Database.

[Column("is_remote_data_archive_enabled")]
[Nullable]
public bool? IsRemoteDataArchiveEnabled { get; set; }

Property Value

bool?

IsReplicated

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

[Column("is_replicated")]
[Nullable]
public bool? IsReplicated { get; set; }

Property Value

bool?

IsSchemaPublished

Only the schema of the object is published.

[Column("is_schema_published")]
[NotNull]
public bool IsSchemaPublished { get; set; }

Property Value

bool

IsSyncTranSubscribed

1 = Table is subscribed using an immediate updating subscription.

[Column("is_sync_tran_subscribed")]
[Nullable]
public bool? IsSyncTranSubscribed { get; set; }

Property Value

bool?

IsTrackedByCdc

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

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

Applies to: Azure SQL Database.

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 Azure SQL Database ledger.

[Column("ledger_type")]
[NotNull]
public byte LedgerType { get; set; }

Property Value

byte

LedgerTypeDesc

Applies to: Azure SQL Database.

The text description of a value in the ledger_type column:

NON_LEDGER_TABLE
HISTORY_TABLE
UPDATABLE_LEDGER_TABLE
APPEND_ONLY_LEDGER_TABLE

[Column("ledger_type_desc")]
[NotNull]
public string LedgerTypeDesc { get; set; }

Property Value

string

LedgerViewID

Applies to: Azure SQL Database.

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

[Column("ledger_view_id")]
[NotNull]
public int LedgerViewID { get; set; }

Property Value

int

LobDataSpaceID

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.

[Column("lob_data_space_id")]
[NotNull]
public int LobDataSpaceID { get; set; }

Property Value

int

LockEscalation

The 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

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

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

Maximum column ID ever used by this table.

[Column("max_column_id_used")]
[NotNull]
public int MaxColumnIDUsed { get; set; }

Property Value

int

ModifyDate

Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when an index on the table or view is created or altered.

[Column("modify_date")]
[NotNull]
public DateTime ModifyDate { get; set; }

Property Value

DateTime

Name

Object name.

[Column("name")]
[NotNull]
public string Name { get; set; }

Property Value

string

Object

objects (sys.objects)

[Association(ThisKey = "ObjectID", OtherKey = "ObjectID", CanBeNull = false)]
public ObjectSchema.Object Object { get; set; }

Property Value

ObjectSchema.Object

ObjectID

Object identification number. Is unique within a database.

[Column("object_id")]
[NotNull]
public int ObjectID { get; set; }

Property Value

int

ParentObjectID

ID of the object to which this object belongs.

0 = Not a child object.

[Column("parent_object_id")]
[NotNull]
public int ParentObjectID { get; set; }

Property Value

int

PrincipalID

ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

Is NULL if there is no alternate individual owner.

Is NULL if the object type is one of the following:

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

TA = Assembly (CLR-integration) trigger

TR = SQL trigger

UQ = UNIQUE constraint

EC = Edge constraint

[Column("principal_id")]
[Nullable]
public int? PrincipalID { get; set; }

Property Value

int?

SchemaID

ID of the schema that the object is contained in.

Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas.

[Column("schema_id")]
[NotNull]
public int SchemaID { get; set; }

Property Value

int

TemporalType

Applies to: SQL Server 2016 (13.x) and later and Azure SQL Database.

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

[Column("temporal_type")]
[Nullable]
public byte? TemporalType { get; set; }

Property Value

byte?

TemporalTypeDesc

Applies to: SQL Server 2016 (13.x) and later and Azure SQL Database.

The text description of the type of table:

NON_TEMPORAL_TABLE

HISTORY_TABLE

SYSTEM_VERSIONED_TEMPORAL_TABLE

[Column("temporal_type_desc")]
[Nullable]
public string? TemporalTypeDesc { get; set; }

Property Value

string

TextInRowLimit

The maximum bytes allowed for text in row.

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

[Column("text_in_row_limit")]
[Nullable]
public int? TextInRowLimit { get; set; }

Property Value

int?

TypeColumn

Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

U = Table (user-defined)

V = View

EC = Edge constraint



Applies to: SQL Server 2012 (11.x) and later.

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

UQ = UNIQUE constraint

X = Extended stored procedure



Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).

ST = STATS_TREE



Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW).

ET = External Table

[Column("type")]
[Nullable]
public string? TypeColumn { get; set; }

Property Value

string

TypeDesc

Description of the object type:

AGGREGATE_FUNCTION

CHECK_CONSTRAINT

CLR_SCALAR_FUNCTION

CLR_STORED_PROCEDURE

CLR_TABLE_VALUED_FUNCTION

CLR_TRIGGER

DEFAULT_CONSTRAINT

EXTENDED_STORED_PROCEDURE

FOREIGN_KEY_CONSTRAINT

INTERNAL_TABLE

PLAN_GUIDE

PRIMARY_KEY_CONSTRAINT

REPLICATION_FILTER_PROCEDURE

RULE

SEQUENCE_OBJECT



Applies to: SQL Server 2012 (11.x) and later.

SERVICE_QUEUE

SQL_INLINE_TABLE_VALUED_FUNCTION

SQL_SCALAR_FUNCTION

SQL_STORED_PROCEDURE

SQL_TABLE_VALUED_FUNCTION

SQL_TRIGGER

SYNONYM

SYSTEM_TABLE

TABLE_TYPE

UNIQUE_CONSTRAINT

USER_TABLE

VIEW

[Column("type_desc")]
[Nullable]
public string? TypeDesc { get; set; }

Property Value

string

UsesAnsiNulls

Table was created with the SET ANSI_NULLS database option ON.

[Column("uses_ansi_nulls")]
[Nullable]
public bool? UsesAnsiNulls { get; set; }

Property Value

bool?