sys.databases (Transact-SQL)sys.databases (Transact-SQL)

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

針對 SQL ServerSQL Server執行個體中的每個資料庫,各包含一個資料列。Contains one row per database in the instance of SQL ServerSQL Server.

如果資料庫不是ONLINE,或AUTO_CLOSE設為ON和資料庫已關閉,某些資料行的值可能NULLIf a database is not ONLINE, or AUTO_CLOSE is set to ON and the database is closed, the values of some columns may be NULL. 如果資料庫是OFFLINE,低權限的使用者看不到 對應的資料列。If a database is OFFLINE, the corresponding row is not visible to low-privileged users. 若要查看對應的資料列,如果資料庫位於OFFLINE,使用者必須具備至少ALTER ANY DATABASE伺服器層級權限,或有CREATE DATABASE中的權限master資料庫。To see the corresponding row if the database is OFFLINE, a user must have at least the ALTER ANY DATABASE server-level permission, or the CREATE DATABASE permission in the master database.

資料行名稱Column name 資料類型Data type 描述Description
namename sysnamesysname 資料庫的名稱,在 SQL ServerSQL Server 執行個體或 Azure SQL DatabaseAzure SQL Database伺服器內是唯一的。Name of database, unique within an instance of SQL ServerSQL Server or within a Azure SQL DatabaseAzure SQL Database server.
database_iddatabase_id intint 資料庫的識別碼,在 SQL ServerSQL Server 執行個體或 Azure SQL DatabaseAzure SQL Database伺服器內是唯一的。ID of the database, unique within an instance of SQL ServerSQL Server or within a Azure SQL DatabaseAzure SQL Database server.
source_database_idsource_database_id intint Non-NULL = 這個資料庫快照集的來源資料庫識別碼。Non-NULL = ID of the source database of this database snapshot.
NULL = 不是資料庫快照集。NULL = Not a database snapshot.
owner_sidowner_sid varbinary(85)varbinary(85) 資料庫外部擁有者的 SID (安全性識別碼),亦即在伺服器註冊所用的識別碼。SID (Security-Identifier) of the external owner of the database, as registered to the server. 誰可以擁有資料庫的相關資訊,請參閱資料庫的 ALTER AUTHORIZATION一節ALTER AUTHORIZATIONFor information about who can own a database, see the ALTER AUTHORIZATION for databases section of ALTER AUTHORIZATION.
create_datecreate_date datetimedatetime 資料庫建立或重新命名的日期。Date the database was created or renamed. 針對tempdb,這個值會變更每次重新啟動伺服器。For tempdb, this value changes every time the server restarts.
compatibility_levelcompatibility_level tinyinttinyint 對應於與行為相容之 SQL ServerSQL Server 版本的整數:Integer corresponding to the version of SQL ServerSQL Server for which behavior is compatible:
| 適用於Value | Applies to
70 | SQL Server 2008SQL Server 2008透過 SQL Server 2008 R2SQL Server 2008 R270 | SQL Server 2008SQL Server 2008 through SQL Server 2008 R2SQL Server 2008 R2
80 | SQL Server 2008SQL Server 2008透過 SQL Server 2008 R2SQL Server 2008 R280 | SQL Server 2008SQL Server 2008 through SQL Server 2008 R2SQL Server 2008 R2
90 | SQL Server 2008SQL Server 2008透過 SQL Server 2012 (11.x)SQL Server 2012 (11.x)90 | SQL Server 2008SQL Server 2008 through SQL Server 2012 (11.x)SQL Server 2012 (11.x)
100 | SQL Server 2008SQL Server 2008透過SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL Database100 | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database
110 | SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL Database110 | SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database
120 | SQL Server 2014 (12.x)SQL Server 2014 (12.x)透過SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL Database120 | SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database
130 | SQL Server 2016 (13.x)SQL Server 2016 (13.x)透過 SQL Server 2017SQL Server 2017130 | SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017
140 | SQL Server 2017 (14.x)SQL Server 2017 (14.x)透過 SQL Server 2017SQL Server 2017140 | SQL Server 2017 (14.x)SQL Server 2017 (14.x) through SQL Server 2017SQL Server 2017
150| SQL Server 2019 (15.x)SQL Server 2019 (15.x)150 | SQL Server 2019 (15.x)SQL Server 2019 (15.x)
collation_namecollation_name sysnamesysname 資料庫的定序。Collation for the database. 它是資料庫中的預設定序。Acts as the default collation in the database.
NULL = 資料庫不在線上,或者 AUTO_CLOSE 設為 ON 且資料庫已關閉。NULL = Database is not online or AUTO_CLOSE is set to ON and the database is closed.
user_accessuser_access tinyinttinyint 使用者存取設定:User-access setting:
0 = 指定了 MULTI_USER0 = MULTI_USER specified
1 = 指定了 SINGLE_USER1 = SINGLE_USER specified
2 = 指定了 RESTRICTED_USER2 = RESTRICTED_USER specified
user_access_descuser_access_desc nvarchar(60)nvarchar(60) 使用者存取設定的描述。Description of user-access setting.
is_read_onlyis_read_only bitbit 1 = 資料庫是 READ_ONLY1 = Database is READ_ONLY
0 = 資料庫是 READ_WRITE0 = Database is READ_WRITE
is_auto_close_onis_auto_close_on bitbit 1 = AUTO_CLOSE 是 ON1 = AUTO_CLOSE is ON
0 = AUTO_CLOSE 是 OFF0 = AUTO_CLOSE is OFF
is_auto_shrink_onis_auto_shrink_on bitbit 1 = AUTO_SHRINK 是 ON1 = AUTO_SHRINK is ON
0 = AUTO_SHRINK 是 OFF0 = AUTO_SHRINK is OFF
statestate tinyinttinyint 值|適用於Value | Applies to
0 = ONLINE0 = ONLINE
1 = RESTORING1 = RESTORING
2 = 正在復原|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20172 = RECOVERING | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
3 = RECOVERY_PENDING | SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20173 = RECOVERY_PENDING | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
4 = SUSPECT4 = SUSPECT
5 = 緊急|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20175 = EMERGENCY | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
6 = 離線|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20176 = OFFLINE | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
7 = 複製| Azure SQL DatabaseAzure SQL Database 使用中的地理複寫Active Geo-Replication7 = COPYING | Azure SQL DatabaseAzure SQL Database 使用中的地理複寫Active Geo-Replication
10 = OFFLINE_SECONDARY| Azure SQL DatabaseAzure SQL Database 使用中的地理複寫Active Geo-Replication10 = OFFLINE_SECONDARY | Azure SQL DatabaseAzure SQL Database 使用中的地理複寫Active Geo-Replication

注意: 對於 Alwayson 資料庫查詢database_state或是database_state_desc的資料行sys.dm_hadr_database_replica_statesNote: For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.
state_descstate_desc nvarchar(60)nvarchar(60) 資料庫狀態的描述。Description of the database state. 請參閱狀態。See state.
is_in_standbyis_in_standby bitbit 還原記錄的資料庫是唯讀資料庫。Database is read-only for restore log.
is_cleanly_shutdownis_cleanly_shutdown bitbit 1 = 資料庫完全關閉;不必在啟動時復原1 = Database shut down cleanly; no recovery required on startup
0 = 資料庫並未完全關閉;必須在啟動時復原0 = Database did not shut down cleanly; recovery is required on startup
is_supplemental_logging_enabledis_supplemental_logging_enabled bitbit 1 = SUPPLEMENTAL_LOGGING 是 ON1 = SUPPLEMENTAL_LOGGING is ON
0 = SUPPLEMENTAL_LOGGING 是 OFF0 = SUPPLEMENTAL_LOGGING is OFF
snapshot_isolation_statesnapshot_isolation_state tinyinttinyint 允許進行的快照集隔離交易狀態,由 ALLOW_SNAPSHOT_ISOLATION 選項設定:State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
0 = 快照集隔離狀態是 OFF (預設值)。0 = Snapshot isolation state is OFF (default). 不接受快照集隔離。Snapshot isolation is disallowed.
1 = 快照集隔離狀態是 ON。1 = Snapshot isolation state ON. 接受快照集隔離。Snapshot isolation is allowed.
2 = 快照集隔離狀態正轉移為 OFF 狀態。2 = Snapshot isolation state is in transition to OFF state. 所有的交易都把自己的修改版本化了。All transactions have their modifications versioned. 新交易無法利用快照集隔離加以啟動。Cannot start new transactions using snapshot isolation. 資料庫仍然保持在轉移為 OFF 狀態的過渡時期,必須等到在執行 ALTER DATABASE 時,所有使用中的交易可以完成為止。The database remains in the transition to OFF state until all transactions that were active when ALTER DATABASE was run can be completed.
3 = 快照集隔離狀態正轉移為 ON 狀態。3 = Snapshot isolation state is in transition to ON state. 新交易都把自己的修改版本化了。New transactions have their modifications versioned. 除非快照集隔離狀態變成 1 (ON),否則交易無法使用快照集隔離。Transactions cannot use snapshot isolation until the snapshot isolation state becomes 1 (ON). 資料庫仍然保持在轉移為 ON 狀態的過渡時期,必須等到在執行 ALTER DATABASE 時,所有使用中的更新交易可以完成為止。The database remains in the transition to ON state until all update transactions that were active when ALTER DATABASE was run can be completed.
snapshot_isolation_state_descsnapshot_isolation_state_desc nvarchar(60)nvarchar(60) 允許進行的快照集隔離交易狀態的描述,由 ALLOW_SNAPSHOT_ISOLATION 選項設定。Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option.
is_read_committed_snapshot_onis_read_committed_snapshot_on bitbit 1 = READ_COMMITTED_SNAPSHOT 選項為 ON。1 = READ_COMMITTED_SNAPSHOT option is ON. 讀取認可隔離等級下的讀取作業是以快照集掃描為基礎,沒有取得鎖定。Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.
0 = READ_COMMITTED_SNAPSHOT 選項為 OFF (預設)。0 = READ_COMMITTED_SNAPSHOT option is OFF (default). 讀取認可隔離等級下的讀取作業是使用共用鎖定。Read operations under the read-committed isolation level use share locks.
recovery_modelrecovery_model tinyinttinyint 所選的復原模式:Recovery model selected:
1 = FULL1 = FULL
2 = BULK_LOGGED2 = BULK_LOGGED
3 = SIMPLE3 = SIMPLE
recovery_model_descrecovery_model_desc nvarchar(60)nvarchar(60) 所選之復原模式的描述。Description of recovery model selected.
page_verify_optionpage_verify_option tinyinttinyint PAGE_VERIFY 選項的設定:Setting of PAGE_VERIFY option:
0 = NONE0 = NONE
1 = TORN_PAGE_DETECTION1 = TORN_PAGE_DETECTION
2 = CHECKSUM2 = CHECKSUM
page_verify_option_descpage_verify_option_desc nvarchar(60)nvarchar(60) PAGE_VERIFY 選項設定的描述。Description of PAGE_VERIFY option setting.
is_auto_create_stats_onis_auto_create_stats_on bitbit 1 = AUTO_CREATE_STATISTICS 是 ON1 = AUTO_CREATE_STATISTICS is ON
0 = AUTO_CREATE_STATISTICS 是 OFF0 = AUTO_CREATE_STATISTICS is OFF
is_auto_create_stats_incremental_onis_auto_create_stats_incremental_on bitbit 表示 Auto Stats 之累加選項的預設設定。Indicates the default setting for the incremental option of auto stats.
0 = 自動建立非累加的統計資料0 = auto create stats are non-incremental
1 = 盡可能自動建立累加的統計資料1 = auto create stats are incremental if possible
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.
is_auto_update_stats_onis_auto_update_stats_on bitbit 1 = AUTO_UPDATE_STATISTICS 是 ON1 = AUTO_UPDATE_STATISTICS is ON
0 = AUTO_UPDATE_STATISTICS 是 OFF0 = AUTO_UPDATE_STATISTICS is OFF
is_auto_update_stats_async_onis_auto_update_stats_async_on bitbit 1 = AUTO_UPDATE_STATISTICS_ASYNC 是 ON1 = AUTO_UPDATE_STATISTICS_ASYNC is ON
0 = AUTO_UPDATE_STATISTICS_ASYNC 是 OFF0 = AUTO_UPDATE_STATISTICS_ASYNC is OFF
is_ansi_null_default_onis_ansi_null_default_on bitbit 1 = ANSI_NULL_DEFAULT 是 ON1 = ANSI_NULL_DEFAULT is ON
0 = ANSI_NULL_DEFAULT 是 OFF0 = ANSI_NULL_DEFAULT is OFF
is_ansi_nulls_onis_ansi_nulls_on bitbit 1 = ANSI_NULLS 是 ON1 = ANSI_NULLS is ON
0 = ANSI_NULLS 是 OFF0 = ANSI_NULLS is OFF
is_ansi_padding_onis_ansi_padding_on bitbit 1 = ANSI_PADDING 是 ON1 = ANSI_PADDING is ON
0 = ANSI_PADDING 是 OFF0 = ANSI_PADDING is OFF
is_ansi_warnings_onis_ansi_warnings_on bitbit 1 = ANSI_WARNINGS 是 ON1 = ANSI_WARNINGS is ON
0 = ANSI_WARNINGS 是 OFF0 = ANSI_WARNINGS is OFF
is_arithabort_onis_arithabort_on bitbit 1 = ARITHABORT 是 ON1 = ARITHABORT is ON
0 = ARITHABORT 是 OFF0 = ARITHABORT is OFF
is_concat_null_yields_null_onis_concat_null_yields_null_on bitbit 1 = CONCAT_NULL_YIELDS_NULL 是 ON1 = CONCAT_NULL_YIELDS_NULL is ON
0 = CONCAT_NULL_YIELDS_NULL 是 OFF0 = CONCAT_NULL_YIELDS_NULL is OFF
is_numeric_roundabort_onis_numeric_roundabort_on bitbit 1 = NUMERIC_ROUNDABORT 是 ON1 = NUMERIC_ROUNDABORT is ON
0 = NUMERIC_ROUNDABORT 是 OFF0 = NUMERIC_ROUNDABORT is OFF
is_quoted_identifier_onis_quoted_identifier_on bitbit 1 = QUOTED_IDENTIFIER 是 ON1 = QUOTED_IDENTIFIER is ON
0 = QUOTED_IDENTIFIER 是 OFF0 = QUOTED_IDENTIFIER is OFF
is_recursive_triggers_onis_recursive_triggers_on bitbit 1 = RECURSIVE_TRIGGERS 是 ON1 = RECURSIVE_TRIGGERS is ON
0 = RECURSIVE_TRIGGERS 是 OFF0 = RECURSIVE_TRIGGERS is OFF
is_cursor_close_on_commit_onis_cursor_close_on_commit_on bitbit 1 = CURSOR_CLOSE_ON_COMMIT 是 ON1 = CURSOR_CLOSE_ON_COMMIT is ON
0 = CURSOR_CLOSE_ON_COMMIT 是 OFF0 = CURSOR_CLOSE_ON_COMMIT is OFF
is_local_cursor_defaultis_local_cursor_default bitbit 1 = CURSOR_DEFAULT 是區域1 = CURSOR_DEFAULT is local
0 = CURSOR_DEFAULT 是全域0 = CURSOR_DEFAULT is global
is_fulltext_enabledis_fulltext_enabled bitbit 1 = 資料庫啟用全文檢索1 = Full-text is enabled for the database
0 = 資料庫停用全文檢索0 = Full-text is disabled for the database
is_trustworthy_onis_trustworthy_on bitbit 1 = 資料庫已被標示為可信任1 = Database has been marked trustworthy
0 = 資料庫尚未標示為可信任0 = Database has not been marked trustworthy
is_db_chaining_onis_db_chaining_on bitbit 1 = 跨資料庫擁有權鏈結是 ON1 = Cross-database ownership chaining is ON
0 = 跨資料庫擁有權鏈結是 OFF0 = Cross-database ownership chaining is OFF
is_parameterization_forcedis_parameterization_forced bitbit 1 = 參數化是 FORCED1 = Parameterization is FORCED
0 = 參數化是 SIMPLE0 = Parameterization is SIMPLE
is_master_key_encrypted_by_serveris_master_key_encrypted_by_server bitbit 1 = 資料庫具有已加密的主要金鑰1 = Database has an encrypted master key
0 = 資料庫沒有已加密的主要金鑰0 = Database does not have an encrypted master key
is_query_store_onis_query_store_on bitbit 1 = 查詢存放區會啟用此資料庫。1 = The query store is enable for this database. 請檢查sys.database_query_store_options檢視的查詢存放區狀態。Check sys.database_query_store_options to view the query store status.
0 = 查詢存放區未啟用0 = The query store is not enabled
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017).
is_publishedis_published bitbit 1 = 資料庫是交易式或快照式複寫拓撲的發行集資料庫1 = Database is a publication database in a transactional or snapshot replication topology
0 = 不是發行集資料庫0 = Is not a publication database
is_subscribedis_subscribed bitbit 不使用這個資料行。This column is not used. 不論資料庫的訂閱者狀態為何,它一定會傳回 0。It will always return 0, regardless of the subscriber status of the database.
is_merge_publishedis_merge_published bitbit 1 = 資料庫是合併式複寫拓撲的發行集資料庫1 = Database is a publication database in a merge replication topology
0 = 不是合併式複寫拓撲的發行集資料庫0 = Is not a publication database in a merge replication topology
is_distributoris_distributor bitbit 1 = 資料庫是複寫拓撲的散發資料庫1 = Database is the distribution database for a replication topology
0 = 不是複寫拓撲的散發資料庫0 = Is not the distribution database for a replication topology
is_sync_with_backupis_sync_with_backup bitbit 1 = 資料庫是標示為利用備份進行複寫同步處理1 = Database is marked for replication synchronization with backup
0 = 不是標示為利用備份進行複寫同步處理0 = Is not marked for replication synchronization with backup
service_broker_guidservice_broker_guid uniqueidentifieruniqueidentifier 這個資料庫的 Service Broker 識別碼。Identifier of the service broker for this database. broker_instance的路由表中的目標。Used as the broker_instance of the target in the routing table.
is_broker_enabledis_broker_enabled bitbit 1 = 這個資料庫中的 Broker,目前正在收送訊息。1 = The broker in this database is currently sending and receiving messages.
0 = 所有傳送的訊息,都會停留在傳輸佇列中,而收到的訊息並不會置於這個資料庫的佇列中。0 = All sent messages will stay on the transmission queue and received messages will not be put on queues in this database.
依預設,還原或附加的資料庫都會停用 Broker。By default, restored or attached databases have the broker disabled. 但資料庫鏡像例外,它會在容錯移轉之後啟用 Broker。The exception to this is database mirroring where the broker is enabled after failover.
log_reuse_waitlog_reuse_wait tinyinttinyint 交易記錄空間的重複利用正等待進行最後一個檢查點的下列其中一個。Reuse of transaction log space is currently waiting on one of the following as of the last checkpoint. 如需詳細說明這些值,請參閱交易記錄For more detailed explanations of these values, see The Transaction Log.
值|適用於Value | Applies to
0 = 無0 = Nothing
1 = 檢查點 (當資料庫使用復原模式,而且擁有記憶體最佳化資料檔案群組時,您應該會看到log_reuse_wait資料行指出檢查點或 xtp_checkpoint。)| SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20171 = Checkpoint (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.) | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
2 = 記錄備份|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20172 = Log Backup | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
3 = 使用中的備份或還原|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20173 = Active backup or restore | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
4 = 使用中交易|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20174 = Active transaction | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
5 = 資料庫鏡像|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20175 = Database mirroring | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
6 = 複寫|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20176 = Replication | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
7 = 建立資料庫快照集|SQL Server 2008SQL Server 2008透過 SQL Server 2017SQL Server 20177 = Database snapshot creation | SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017
8 = 記錄掃描8 = Log scan
9 = Alwayson 可用性群組次要複本會將此資料庫的交易記錄檔記錄套用到對應的次要資料庫。9 = An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. |SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過 SQL Server 2017SQL Server 2017| SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
9 = 其他 (暫時性) | ,且包含 SQL Server 2008 R2SQL Server 2008 R29 = Other (Transient) | Up to, and including SQL Server 2008 R2SQL Server 2008 R2
10 = 僅供內部使用|SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過 SQL Server 2017SQL Server 201710 = For internal use only | SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
11 = 僅供內部使用|SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過 SQL Server 2017SQL Server 201711 = For internal use only | SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
12 = 僅供內部使用|SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過 SQL Server 2017SQL Server 201712 = For internal use only | SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
13 = 最舊的頁面|SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過 SQL Server 2017SQL Server 201713 = Oldest page | SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
14 = 其他|SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過 SQL Server 2017SQL Server 201714 = Other | SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
16 = XTP_CHECKPOINT (當資料庫使用復原模式,而且擁有記憶體最佳化資料檔案群組時,您應該預期看見 log_reuse_wait 資料行指出檢查點或 xtp_checkpoint)。| SQL Server 2014 (12.x)SQL Server 2014 (12.x)透過 SQL Server 2017SQL Server 201716 = XTP_CHECKPOINT (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.) | SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017
log_reuse_wait_desclog_reuse_wait_desc nvarchar(60)nvarchar(60) 描述交易記錄空間的重複利用正等待最後一個檢查點。Description of reuse of transaction log space is currently waiting on as of the last checkpoint.
is_date_correlation_onis_date_correlation_on bitbit 1 = DATE_CORRELATION_OPTIMIZATION 是 ON1 = DATE_CORRELATION_OPTIMIZATION is ON
0 = DATE_CORRELATION_OPTIMIZATION 是 OFF0 = DATE_CORRELATION_OPTIMIZATION is OFF
is_cdc_enabledis_cdc_enabled bitbit 1 = 資料庫已啟用異動資料擷取。1 = Database is enabled for change data capture. 如需詳細資訊,請參閱 < sys.sp_cdc_enable_db (TRANSACT-SQL)For more information, see sys.sp_cdc_enable_db (Transact-SQL).
is_encryptedis_encrypted bitbit 指出是否已加密的資料庫 (反映上次使用所設定的狀態ALTER DATABASE SET ENCRYPTION子句)。Indicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause). 可為下列其中一個值:Can be one of the following values:
1 = 已加密1 = Encrypted
0 = 未加密0 = Not Encrypted
如需資料庫加密的詳細資訊,請參閱透明資料加密 (TDE)For more information about database encryption, see Transparent Data Encryption (TDE).
如果資料庫正在進行解密,is_encrypted顯示值為 0。If the database is in the process of being decrypted, is_encrypted shows a value of 0. 您可以使用,以查看加密程序的狀態sys.dm_database_encryption_keys動態管理檢視。You can see the state of the encryption process by using the sys.dm_database_encryption_keys dynamic management view.
is_honor_broker_priority_onis_honor_broker_priority_on bitbit 指出資料庫是否接受交談優先權 (反映上次使用所設定的狀態ALTER DATABASE SET HONOR_BROKER_PRIORITY子句)。Indicates whether the database honors conversation priorities (reflects the state last set by using the ALTER DATABASE SET HONOR_BROKER_PRIORITY clause). 可為下列其中一個值:Can be one of the following values:
1 = HONOR_BROKER_PRIORITY 為 ON1 = HONOR_BROKER_PRIORITY is ON
0 = HONOR_BROKER_PRIORITY 為 OFF0 = HONOR_BROKER_PRIORITY is OFF
replica_idreplica_id uniqueidentifieruniqueidentifier 資料庫正在參與之可用性群組 (如果有) 的本機 AlwaysOn 可用性群組Always On availability groups可用性複本的唯一識別碼。Unique identifier of the local AlwaysOn 可用性群組Always On availability groups availability replica of the availability group, if any, in which the database is participating.
NULL = 資料庫不是可用性群組中可用性複本的一部分。NULL = database is not part of an availability replica of in availability group.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
group_database_idgroup_database_id uniqueidentifieruniqueidentifier Always On 可用性群組內,如果任何資料庫正在參與之資料庫的唯一識別碼。Unique identifier of the database within an Always On availability group, if any, in which the database is participating. group_database_id是相同的主要複本上和所在資料庫已聯結至可用性群組的每個次要複本的這個資料庫。group_database_id is the same for this database on the primary replica and on every secondary replica on which the database has been joined to the availability group.
NULL = 資料庫不是任何可用性群組中可用性複本的一部分。NULL = database is not part of an availability replica in any availability group.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
resource_pool_idresource_pool_id intint 對應到這個資料庫之資源集區的識別碼。The id of the resource pool that is mapped to this database. 這個資源集區會控制可供這個資料庫中記憶體最佳化資料表使用的記憶體總量。This resource pool controls total memory available to memory-optimized tables in this database.
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017
default_language_lciddefault_language_lcid smallintsmallint 表示自主資料庫預設語言的地區設定識別碼 (LCID)。Indicates the local id (lcid) of the default language of a contained database.
注意: 做為函式設定 default language 伺服器組態選項sp_configureNote: Functions as the Configure the default language Server Configuration Option of sp_configure. 這個值是null非自主資料庫。This value is null for a non-contained database.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
default_language_namedefault_language_name nvarchar(128)nvarchar(128) 表示自主資料庫的預設語言。Indicates the default language of a contained database.
這個值是null非自主資料庫。This value is null for a non-contained database.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
default_fulltext_language_lciddefault_fulltext_language_lcid intint 表示自主資料庫的預設全文檢索語言的地區設定識別碼 (lcid)。Indicates the locale id (lcid) of the default fulltext language of the contained database.
注意: 做為預設值設定預設全文檢索語言伺服器組態選項sp_configureNote: Functions as the default Configure the default full-text language Server Configuration Option of sp_configure. 這個值是null非自主資料庫。This value is null for a non-contained database.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
default_fulltext_language_namedefault_fulltext_language_name nvarchar(128)nvarchar(128) 表示自主資料庫的預設全文檢索語言。Indicates the default fulltext language of the contained database.
這個值是null非自主資料庫。This value is null for a non-contained database.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
is_nested_triggers_onis_nested_triggers_on bitbit 指出自主資料庫是否允許巢狀觸發程序。Indicates whether or not nested triggers are allowed in the contained database.
0 = 不允許巢狀觸發程序。0 = nested triggers are not allowed
1 = 允許巢狀觸發程序。1 = nested triggers are allowed
注意: 做為函式設定 nested 的 triggers 伺服器組態選項sp_configureNote: Functions as the Configure the nested triggers Server Configuration Option of sp_configure. 這個值是null非自主資料庫。This value is null for a non-contained database. 請參閱sys.configurations (TRANSACT-SQL) 如需詳細資訊。See sys.configurations (Transact-SQL) for further information.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
is_transform_noise_words_onis_transform_noise_words_on bitbit 指出自主資料庫中是否應該轉換非搜尋字。Indicates whether or noise words should be transformed in the contained database.
0 = 不應該轉換非搜尋字。0 = noise words should not be transformed.
1 = 應該轉換非搜尋字。1 = noise words should be transformed.
注意: 做為函式轉換非搜尋字伺服器組態選項sp_configureNote: Functions as the transform noise words Server Configuration Option of sp_configure. 這個值是null非自主資料庫。This value is null for a non-contained database. 請參閱sys.configurations (TRANSACT-SQL) 如需詳細資訊。See sys.configurations (Transact-SQL) for further information.
適用於SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
two_digit_year_cutofftwo_digit_year_cutoff smallintsmallint 表示 1753 與 9999 之間的數值,代表將二位數年份解譯為四位數年份時的截斷年份 (Cutoff Year)。Indicates a value of a number between 1753 and 9999 to represent the cutoff year for interpreting two-digit years as four-digit years.
注意: 做為函式設定 two digit year cutoff 伺服器組態選項sp_configureNote: Functions as the Configure the two digit year cutoff Server Configuration Option of sp_configure. 這個值是null非自主資料庫。This value is null for a non-contained database. 請參閱sys.configurations (TRANSACT-SQL) 如需詳細資訊。See sys.configurations (Transact-SQL) for further information.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
containmentcontainment 非 null 的 tinyinttinyint not null 指示資料庫的內含項目狀態。Indicates the containment status of the database.
0 = 資料庫內含項目已關閉。0 = database containment is off. 適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
1 = 資料庫處於部分內含項目適用於:SQL Server 2012 (11.x)SQL Server 2012 (11.x)透過 SQL Server 2017SQL Server 20171 = database is in partial containment Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017
containment_desccontainment_desc nvarchar(60) not nullnvarchar(60) not null 指示資料庫的內含項目狀態。Indicates the containment status of the database.
NONE = 舊版資料庫 (零個內含項目)NONE = legacy database (zero containment)
PARTIAL = 部分自主資料庫PARTIAL = partially contained database
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
target_recovery_time_in_secondstarget_recovery_time_in_seconds intint 復原資料庫的預估時間 (以秒為單位)。The estimated time to recover the database, in seconds. 可為 Null。Nullable.
適用於SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017) 和 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database
delayed_durabilitydelayed_durability intint 延遲的持久性設定:The delayed durability setting:
0 = 停用0 = DISABLED
1 = 允許1 = ALLOWED
2 = 強制2 = FORCED
如需詳細資訊,請參閱控制交易持久性For more information, see Control Transaction Durability.
適用對象SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.
delayed_durability_descdelayed_durability_desc nvarchar(60)nvarchar(60) 延遲的持久性設定:The delayed durability setting:
DISABLEDDISABLED
ALLOWEDALLOWED
FORCEDFORCED
適用對象SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017),以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017) and Azure SQL DatabaseAzure SQL Database.
is_memory_optimized_elevate_to_snapshot_onis_memory_optimized_elevate_to_snapshot_on bitbit 當工作階段設定 TRANSACTION ISOLATION LEVEL 設定為較低的隔離等級 READ COMMITTED 或 READ UNCOMMITTED 時,會使用 SNAPSHOT 隔離存取記憶體最佳化的資料表。Memory-optimized tables are accessed using SNAPSHOT isolation when the session setting TRANSACTION ISOLATION LEVEL is set to a lower isolation level, READ COMMITTED or READ UNCOMMITTED.
1 = 最低隔離等級為 SNAPSHOT。1 = Minimum isolation level is SNAPSHOT.
0 = 不提高隔離等級。0 = Isolation level is not elevated.
is_federation_memberis_federation_member bitbit 表示資料庫是否為同盟的成員。Indicates if the database is a member of a federation.
適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database
is_remote_data_archive_enabledis_remote_data_archive_enabled bitbit 指出是否要延展的資料庫。Indicates whether the database is stretched.
0 = 資料庫不是已啟用延展功能。0 = The database is not Stretch-enabled.
1 = 資料庫是已啟用延展功能。1 = The database is Stretch-enabled.
適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017
如需詳細資訊,請參閱 Stretch DatabaseFor more information, see Stretch Database.
is_mixed_page_allocation_onis_mixed_page_allocation_on bitbit 指出是否在資料庫中資料表和索引可以初始頁面從配置混合範圍。Indicates whether tables and indexes in the database can allocate initial pages from mixed extents.
0 = 資料表,並在資料庫中的索引一律從一致範圍配置初始頁面。0 = Tables and indexes in the database always allocate initial pages from uniform extents.
1 = 資料表和資料庫中的索引可以從混合範圍配置初始頁面。1 = Tables and indexes in the database can allocate initial pages from mixed extents.
適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017
如需詳細資訊,請參閱的 SET MIXED_PAGE_ALLOCATION 選項ALTER DATABASE SET 選項(TRANSACT-SQL)For more information, see the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE SET Options (Transact-SQL).
is_temporal_retention_enabledis_temporal_retention_enabled bitbit 指出是否已啟用時態保留原則的清除工作。Indicates whether temporal retention policy cleanup task is enabled.
適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database
catalog_collation_typecatalog_collation_type intint 目錄定序設定:The catalog collation setting:
0 = DATABASE_DEFAULT0 = DATABASE_DEFAULT
2 = SQL_Latin_1_General_CP1_CI_AS2 = SQL_Latin_1_General_CP1_CI_AS
適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database
catalog_collation_type_desccatalog_collation_type_desc nvarchar(60)nvarchar(60) 目錄定序設定:The catalog collation setting:
COLLATEDATABASE_DEFAULT
SQL_Latin_1_General_CP1_CI_ASSQL_Latin_1_General_CP1_CI_AS
適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database
is_result_set_caching_onis_result_set_caching_on intint 1 = is_result_set_caching_on 位於1 = is_result_set_caching_on is on
0 = is_result_set_caching_on is off0 = is_result_set_caching_on is off
適用於:Azure SQL 資料倉儲 Gen2。Applies to: Azure SQL Data Warehouse Gen2. 雖然此功能正推出到所有區域,請檢查部署至您的執行個體和最新版本Azure SQL DW 版本資訊功能可用性。While this features is being rolled out to all regions, please check the version deployed to your instance and the latest Azure SQL DW release notes for feature availability.

PermissionsPermissions

如果呼叫端sys.databases不是資料庫的擁有者,而且資料庫不是mastertempdb,請參閱對應的資料列所需的最低權限會ALTER ANY DATABASEVIEW ANY DATABASE伺服器層級權限或CREATE DATABASE中的權限master資料庫。If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or the VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. 呼叫端連接的資料庫可以永遠會被視為在sys.databasesThe database to which the caller is connected can always be viewed in sys.databases.

重要

根據預設,公用角色具備VIEW ANY DATABASE權限,允許所有登入查看資料庫資訊。By default, the public role has the VIEW ANY DATABASE permission, allowing all logins to see database information. 若要封鎖登入,以偵測資料庫的能力REVOKE``VIEW ANY DATABASE權限public,或DENY``VIEW ANY DATABASE個別登入的權限。To block a login from the ability to detect a database, REVOKE the VIEW ANY DATABASE permission from public, or DENY the VIEW ANY DATABASE permission for individual logins.

Azure SQL Database 備註Azure SQL Database Remarks

Azure SQL DatabaseAzure SQL Database此檢視位於master資料庫和使用者資料庫中。In Azure SQL DatabaseAzure SQL Database this view is available in the master database and in user databases. master資料庫,此檢視傳回的資訊master資料庫和伺服器上的所有使用者資料庫。In the master database, this view returns the information on the master database and all user databases on the server. 在使用者資料庫中,這個檢視只會傳回有關目前資料庫和 master 資料庫的資訊。In a user database, this view returns information only on the current database and the master database.

使用建立新資料庫所在的 Azure SQL DatabaseAzure SQL Database 伺服器上 master 資料庫中的 sys.databases 檢視。Use the sys.databases view in the master database of the Azure SQL DatabaseAzure SQL Database server where the new database is being created. 資料庫複製開始之後,您可以查詢sys.databasessys.dm_database_copies檢視從master目的地伺服器,以擷取有關複製進度的詳細資訊的資料庫。After the database copy starts, you can query the sys.databases and the sys.dm_database_copies views from the master database of the destination server to retrieve more information about the copying progress.

範例Examples

A.A. 查詢 sys.databases 檢視Query the sys.databases view

下列範例會傳回在可用的資料行數sys.databases檢視。The following example returns a few of the columns available in the sys.databases view.

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc  
FROM sys.databases;  

B.B. 檢查 SQL DatabaseSQL Database中的複製狀態Check the copying status in SQL DatabaseSQL Database

下列範例會查詢sys.databasessys.dm_database_copies檢視,以傳回資料庫的相關資訊複製作業。The following example queries the sys.databases and sys.dm_database_copies views to return information about a database copy operation.

適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database

-- Execute from the master database.  
SELECT a.name, a.state_desc, b.start_date, b.modify_date, b.percentage_complete  
FROM sys.databases AS a  
INNER JOIN sys.dm_database_copies AS b ON a.database_id = b.database_id  
WHERE a.state = 7;  

C.C. 簽入時態保留原則狀態 SQL DatabaseSQL DatabaseCheck the temporal retention policy status in SQL DatabaseSQL Database

下列範例會查詢sys.databases傳回資訊是否已啟用時態保留清除工作。The following example queries the sys.databases to return information whether temporal retention cleanup task is enabled. 請注意,還原作業後時態保留預設會停用。Be aware that after restore operation temporal retention is disabled by default. 使用ALTER DATABASE若要明確啟用它。Use ALTER DATABASE to enable it explicitly.

適用於Azure SQL DatabaseAzure SQL DatabaseApplies to: Azure SQL DatabaseAzure SQL Database

-- Execute from the master database.  
SELECT a.name, a.is_temporal_history_retention_enabled 
FROM sys.databases AS a;

另請參閱See Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL) sys.database_mirroring_witnesses (Transact-SQL)
sys.database_recovery_status (-SQL&#41;) sys.database_recovery_status (Transact-SQL)
資料庫和檔案目錄檢視 (Transact-SQL) Databases and Files Catalog Views (Transact-SQL)
sys.dm_database_copies (Azure SQL Database)sys.dm_database_copies (Azure SQL Database)