sys.tables (Transact-SQL)sys.tables (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

針對 SQL ServerSQL Server 中的每個使用者資料表,各傳回一個資料列。Returns a row for each user table in SQL ServerSQL Server.

資料行名稱Column name 資料類型Data type 描述Description
<繼承的資料行><inherited columns> 如需此視圖所繼承之資料行的清單,請參閱sys.databases (transact-sql)For a list of columns that this view inherits, see sys.objects (Transact-SQL).
lob_data_space_idlob_data_space_id intint 非零值是存放這份資料表的大型物件二進位 (LOB) 資料之資料空間 (檔案群組或分割區配置) 的識別碼。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. LOB 資料類型的範例包括Varbinary (max)Varchar (max)geographyxmlExamples of LOB data types include varbinary(max), varchar(max), geography, or xml.

0 = 資料表沒有 LOB 資料。0 = The table does not LOB data.
filestream_data_space_idfilestream_data_space_id intint 這是 FILESTREAM 檔案群組的資料空間識別碼,或是由 FILESTREAM 檔案群組所組成的分割區配置。Is the data space ID for a FILESTREAM filegroup or a partition scheme that consists of FILESTREAM filegroups.

若要報告 FILESTREAM 檔案群組的名稱,請執行查詢SELECT FILEGROUP_NAME (filestream_data_space_id) FROM sys.tablesTo report the name of a FILESTREAM filegroup, execute the query SELECT FILEGROUP_NAME (filestream_data_space_id) FROM sys.tables.

sys.tables 可以聯結到 filestream_data_space_id = data_space_id 上的下列檢視表。sys.tables can be joined to the following views on filestream_data_space_id = data_space_id.

-sys. filegroup- sys.filegroups

-sys. partition_schemes- sys.partition_schemes

-sys. 索引- sys.indexes

-sys. allocation_units- sys.allocation_units

-sys. fulltext_catalogs- sys.fulltext_catalogs

-sys. data_spaces- sys.data_spaces

-sys. destination_data_spaces- sys.destination_data_spaces

-sys. master_files- sys.master_files

-sys. database_files- sys.database_files

-backupfilegroup (聯結 filegroup_id)- backupfilegroup (join on filegroup_id)
max_column_id_usedmax_column_id_used intint 這份資料表用過的最大資料行識別碼。Maximum column ID ever used by this table.
lock_on_bulk_loadlock_on_bulk_load bitbit 資料表在大量載入時會予以鎖定。Table is locked on bulk load. 如需詳細資訊,請參閱 sp_tableoption (Transact-SQL)For more information, see sp_tableoption (Transact-SQL).
uses_ansi_nullsuses_ansi_nulls bitbit 資料表是在 SET ANSI_NULLS 資料庫選項為 ON 的情況下加以建立。Table was created with the SET ANSI_NULLS database option ON.
is_replicatedis_replicated bitbit 1 = 利用快照式複寫或異動複寫來發行資料表。1 = Table is published using snapshot replication or transactional replication.
has_replication_filterhas_replication_filter bitbit 1 = 資料表有一項複寫篩選。1 = Table has a replication filter.
is_merge_publishedis_merge_published bitbit 1 = 資料表是利用合併式複寫來發行。1 = Table is published using merge replication.
is_sync_tran_subscribedis_sync_tran_subscribed bitbit 1 = 資料表是利用立即更新訂閱來訂閱。1 = Table is subscribed using an immediate updating subscription.
has_unchecked_assembly_datahas_unchecked_assembly_data bitbit 1 = 資料表包含保存資料,這些保存資料會隨著上次 ALTER ASSEMBLY 期間變更定義的組件而不同。1 = Table contains persisted data that depends on an assembly whose definition changed during the last ALTER ASSEMBLY. 在下次 DBCC CHECKDB 或 DBCC CHECKTABLE 順利完成之後,將重設為 0。Will be reset to 0 after the next successful DBCC CHECKDB or DBCC CHECKTABLE.
text_in_row_limittext_in_row_limit intint "text in row" 所允許的最大位元組數目。The maximum bytes allowed for text in row.

0 = 未設定 "text in row" 選項。0 = Text in row option is not set. 如需詳細資訊,請參閱 sp_tableoption (Transact-SQL)For more information, see sp_tableoption (Transact-SQL).
large_value_types_out_of_rowlarge_value_types_out_of_row bitbit 1 = 大數值類型是以 out-of-row 的方式來儲存。1 = Large value types are stored out-of-row. 如需詳細資訊,請參閱 sp_tableoption (Transact-SQL)For more information, see sp_tableoption (Transact-SQL).
is_tracked_by_cdcis_tracked_by_cdc bitbit 1 = 資料表已啟用異動資料擷取。1 = Table is enabled for change data capture. 如需詳細資訊,請參閱sp_cdc_enable_table (transact-sql)For more information, see sys.sp_cdc_enable_table (Transact-SQL).
lock_escalationlock_escalation tinyinttinyint 資料表之 LOCK_ESCALATION 選項的值:The value of the LOCK_ESCALATION option for the table:

0 = TABLE0 = TABLE

1 = DISABLE1 = DISABLE

2 = AUTO2 = AUTO
lock_escalation_desclock_escalation_desc Nvarchar (60)nvarchar(60) 資料表之 lock_escalation 選項的文字描述。A text description of the lock_escalation option for the table. 可能的值為:TABLE、AUTO 和 DISABLE。Possible values are: TABLE, AUTO, and DISABLE.
is_filetableis_filetable bitbit 適用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later and Azure SQL DatabaseAzure SQL Database.

1 = 資料表是 FileTable。1 = Table is a FileTable.

如需有關 FileTable 的詳細資訊,請參閱 FileTables (SQL Server)For more information about FileTables, see FileTables (SQL Server).
持久性durability tinyinttinyint 適用于: SQL Server 2014 (12.x)SQL Server 2014 (12.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and Azure SQL DatabaseAzure SQL Database.

以下是可能的值:The following are possible values:

0 = SCHEMA_AND_DATA0 = SCHEMA_AND_DATA

1 = SCHEMA_ONLY1 = SCHEMA_ONLY

預設值為 0 值。The value of 0 is the default value.
durability_descdurability_desc Nvarchar (60)nvarchar(60) 適用于: SQL Server 2014 (12.x)SQL Server 2014 (12.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and Azure SQL DatabaseAzure SQL Database.

以下是可能的值:The following are the possible values:

SCHEMA_ONLYSCHEMA_ONLY

SCHEMA_AND_DATASCHEMA_AND_DATA

SCHEMA_AND_DATA 的值表示資料表是持久、記憶體中的資料表。The value of SCHEMA_AND_DATA indicates that the table is a durable, in-memory table. SCHEMA_AND_DATA 是記憶體最佳化資料表的預設值。SCHEMA_AND_DATA is the default value for memory optimized tables. SCHEMA_ONLY 值表示,在具有記憶體最佳化物件的資料庫重新啟動時,資料表資料不會保存。The value of SCHEMA_ONLY indicates that the table data will not be persisted upon restart of the database with memory optimized objects.
is_memory_optimizedis_memory_optimized bitbit 適用于: SQL Server 2014 (12.x)SQL Server 2014 (12.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) and later and Azure SQL DatabaseAzure SQL Database.

以下是可能的值:The following are the possible values:

0 = 不是記憶體最佳化的。0 = not memory optimized.

1 = 是記憶體最佳化的。1 = is memory optimized.

預設值是 0 值。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.
temporal_typetemporal_type tinyinttinyint 適用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later and Azure SQL DatabaseAzure SQL Database.

代表資料表類型的數值:The numeric value representing the type of table:

0 = NON_TEMPORAL_TABLE0 = NON_TEMPORAL_TABLE

1 = HISTORY_TABLE1 = HISTORY_TABLE

2 = SYSTEM_VERSIONED_TEMPORAL_TABLE2 = SYSTEM_VERSIONED_TEMPORAL_TABLE
temporal_type_desctemporal_type_desc Nvarchar (60)nvarchar(60) 適用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later and Azure SQL DatabaseAzure SQL Database.

資料表類型的文字描述:The text description of the type of table:

NON_TEMPORAL_TABLENON_TEMPORAL_TABLE

HISTORY_TABLEHISTORY_TABLE

SYSTEM_VERSIONED_TEMPORAL_TABLESYSTEM_VERSIONED_TEMPORAL_TABLE
history_table_idhistory_table_id intint 適用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later and Azure SQL DatabaseAzure SQL Database.

當(2,4)中的 temporal_type 傳回維護歷程記錄資料之資料表的 object_id 時,則為,否則會傳回 Null。When temporal_type IN (2, 4) returns object_id of the table that maintains historical data, otherwise returns NULL.
is_remote_data_archive_enabledis_remote_data_archive_enabled bitbit 適用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更新版本和Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later and Azure SQL DatabaseAzure SQL Database

指出資料表是否已啟用 Stretch。Indicates whether the table is Stretch-enabled.

0 = 資料表未啟用 Stretch。0 = The table is not Stretch-enabled.

1 = 資料表已啟用 Stretch。1 = The table is Stretch-enabled.

如需詳細資訊,請參閱 Stretch DatabaseFor more info, see Stretch Database.
is_externalis_external bitbit 適用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更新版本Azure SQL DatabaseAzure SQL Database、和Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later, Azure SQL DatabaseAzure SQL Database, and Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW).

表示資料表是外部資料表。Indicates table is an external table.

0 = 資料表不是外部資料表。0 = The table is not an external table.

1 = 資料表是外部資料表。1 = The table is an external table.
history_retention_periodhistory_retention_period intint 適用于: Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database.

數值,表示時態歷程記錄保留期間的持續時間,以 history_retention_period_unit 指定的單位。The numeric value representing duration of the temporal history retention period in units specified with history_retention_period_unit.
history_retention_period_unithistory_retention_period_unit intint 適用于: Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database.

代表時態性歷程記錄保留週期單位之類型的數值。The numeric value representing type of temporal history retention period unit.

-1:無限-1 :INFINITE

3:日3: DAY

4:周4: WEEK

5:月5: MONTH

6:年6: YEAR
history_retention_period_unit_deschistory_retention_period_unit_desc Nvarchar (10)nvarchar(10) 適用于: Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database.

時態性記錄保留週期單位之類型的文字描述。The text description of type of temporal history retention period unit.

INFINITEINFINITE

DAYDAY

WEEKWEEK

MONTH

YEAR
is_nodeis_node bitbit 適用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL DatabaseAzure SQL Database.

1 = 這是圖形節點資料表。1 = This is a graph Node table.

0 = 這不是圖形節點資料表。0 = This is not a graph Node table.
is_edgeis_edge bitbit 適用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x) and Azure SQL DatabaseAzure SQL Database.

1 = 這是圖形邊緣資料表。1 = This is a graph Edge table.

0 = 這不是圖形邊緣資料表。0 = This is not a graph Edge table.

權限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. 如需相關資訊,請參閱 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

範例Examples

下列範例會傳回沒有主索引鍵的所有使用者資料表。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.

適用于: SQL Server 2016 (13.x)SQL Server 2016 (13.x)和更新版本Azure SQL DatabaseAzure SQL Database和。Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) and later and Azure SQL DatabaseAzure SQL Database.

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  

下列範例會顯示如何公開時態性記錄保留的資訊。The following example shows how information on temporal history retention can be exposed.

適用于: Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database.

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 

另請參閱See Also

(Transact-sql)的物件目錄檢視 Object Catalog Views (Transact-SQL)
目錄檢視 (Transact-SQL) Catalog Views (Transact-SQL)
DBCC CHECKDB (Transact-sql) DBCC CHECKDB (Transact-SQL)
DBCC CHECKTABLE (Transact-sql) DBCC CHECKTABLE (Transact-SQL)
查詢 SQL Server 系統目錄常見問題 Querying the SQL Server System Catalog FAQ
記憶體內部 OLTP (記憶體內部最佳化)In-Memory OLTP (In-Memory Optimization)