Skip to content

Latest commit

 

History

History
134 lines (115 loc) · 14 KB

sys-tables-transact-sql.md

File metadata and controls

134 lines (115 loc) · 14 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.tables (Transact-SQL)
sys.tables returns a row for each user table in a database.
rwestMSFT
randolphwest
03/27/2024
sql
system-objects
reference
sys.tables_TSQL
sys.tables
sys.tables catalog view
TSQL
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

sys.tables (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

Returns a row for each user table in a [!INCLUDE ssNoVersion] database.

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 doesn't have LOB data.
filestream_data_space_id 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)
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 isn't 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 A 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: [!INCLUDE ssSQL11] and later versions, and [!INCLUDE ssazure-sqldb]
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: [!INCLUDE ssSQL14] and later versions, and [!INCLUDE ssazure-sqldb].
durability tinyint The following are possible values:

0 = SCHEMA_AND_DATA
1 = SCHEMA_ONLY

A value of 0 is the default value.

Applies to: [!INCLUDE ssSQL14] and later versions, and [!INCLUDE ssazure-sqldb]
durability_desc 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: [!INCLUDE ssSQL14] and later versions, and [!INCLUDE ssazure-sqldb]
temporal_type 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: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssazure-sqldb]
temporal_type_desc nvarchar(60) The text description of the type of table:

NON_TEMPORAL_TABLE
HISTORY_TABLE
SYSTEM_VERSIONED_TEMPORAL_TABLE

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssazure-sqldb]
history_table_id 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: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssazure-sqldb]
is_remote_data_archive_enabled 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.

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssazure-sqldb]
is_external bit Indicates table is an external table.

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

Applies to: [!INCLUDE sssql16-md] and later versions, [!INCLUDE ssazure-sqldb], and [!INCLUDE ssazuresynapse-md]
history_retention_period int The numeric value representing duration of the temporal history retention period in units specified with history_retention_period_unit.

Applies to: [!INCLUDE sssql17-md] and later versions, and [!INCLUDE ssazure-sqldb]
history_retention_period_unit 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: [!INCLUDE sssql17-md] and later versions, and [!INCLUDE ssazure-sqldb]
history_retention_period_unit_desc nvarchar(10) The text description of type of temporal history retention period unit.

INFINITE
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
YEAR

Applies to: [!INCLUDE sssql17-md] and later versions, and [!INCLUDE ssazure-sqldb]
is_node bit 1 = Graph node table.
0 = Not a graph node table.

Applies to: [!INCLUDE sssql17-md.md] and later versions, and [!INCLUDE ssazure-sqldb]
is_edge bit 1 = Graph edge table.
0 = Not a graph edge table.

Applies to: [!INCLUDE sssql17-md.md] and later versions, and [!INCLUDE ssazure-sqldb]
data_retention_period 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
data_retention_period_unit 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
data_retention_period_unit_desc 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
ledger_type 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: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssazure-sqldb]
ledger_type_desc 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: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssazure-sqldb]
ledger_view_id int When ledger_type IN (2, 3) returns object_id of the ledger view, otherwise returns NULL.

Applies to: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssazure-sqldb]
is_dropped_ledger_table bit Indicates a ledger table that was dropped.

Applies to: [!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssazure-sqldb]

Permissions

[!INCLUDE ssCatViewPerm] For more information, see Metadata Visibility Configuration.

Examples

A. Return all user tables without a primary key

The following example returns all of the user tables that don't 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

B. List temporal data related tables

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

Applies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssazure-sqldb].

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;

C. List information about temporal history retention

The following example shows how information on temporal history retention can be exposed.

Applies to: [!INCLUDE sssql17-md] and later versions, and [!INCLUDE ssazure-sqldb].

SELECT DB.is_temporal_history_retention_enabled,
    SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
    T1.name AS TemporalTableName,
    SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
    T2.name AS HistoryTableName,
    T1.history_retention_period,
    T1.history_retention_period_unit_desc
FROM sys.tables T1
OUTER APPLY (
    SELECT is_temporal_history_retention_enabled
    FROM sys.databases
    WHERE name = DB_NAME()
) DB
LEFT JOIN sys.tables T2
    ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

Related content