DATABASEPROPERTYEX (Transact-SQL)DATABASEPROPERTYEX (Transact-SQL)

本主題適用於: 是SQL Server (從 2008 開始)是Azure SQL Database是Azure SQL 資料倉儲 是平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

傳回 SQL ServerSQL Server 中指定資料庫之指定資料庫選項或屬性的目前設定。Returns the current setting of the specified database option or property for the specified database in SQL ServerSQL Server.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

DATABASEPROPERTYEX ( database , property )  

引數Arguments

databasedatabase
這是代表傳回具名的屬性資訊所屬的資料庫名稱的運算式。Is an expression that represents the name of the database for which to return the named property information. 資料庫nvarchar (128)database is nvarchar(128).
針對 SQL DatabaseSQL Database,必須是目前資料庫的名稱。For SQL DatabaseSQL Database, must be the name of the current database. 如果提供了不同的資料庫名稱,則對所有屬性傳回 NULL。Returns NULL for all properties if a different database name is provided.

屬性property
這是代表要傳回之資料庫屬性的運算式。Is an expression that represents the name of the database property to return. 屬性varchar (128),而且可以是下列值之一。property is varchar(128), and can be one of the following values. 傳回型別是sql_variantThe return type is sql_variant. 下表顯示了每一屬性值的基底資料型別。The following table shows the base data type for each property value.

注意

如果沒有啟動資料庫, SQL ServerSQL Server 透過直接存取資料庫 (而非從中繼資料中擷取值) 所擷取的屬性會傳回 NULL。If the database is not started, properties that the SQL ServerSQL Server retrieves by accessing the database directly instead of retrieving the value from metadata will return NULL. 也就是說,如果資料庫的 AUTO_CLOSE 設成 ON,否則資料庫會離線。That is, if the database has AUTO_CLOSE set to ON, or the database is otherwise offline.

屬性Property DescriptionDescription 傳回的值Value returned
定序Collation 資料庫的預設定序名稱。Default collation name for the database. 定序名稱Collation name

NULL = 資料庫未啟動。NULL = Database is not started.

基底資料型別: nvarchar (128)Base data type: nvarchar(128)
ComparisonStyleComparisonStyle Windows 的定序比較樣式。The Windows comparison style of the collation. ComparisonStyle 是使用下列的值為可能的樣式所計算的點陣圖。ComparisonStyle is a bitmap that is calculated by using the following values for the possible styles.

忽略大小寫: 1Ignore case : 1

忽略腔調字: 2Ignore accent : 2

忽略假名: 65536Ignore Kana : 65536

忽略寬度: 131072Ignore width : 131072



例如,預設值 196609 是結合「忽略大小寫」、「忽略假名」和「忽略寬度」等選項的結果。For example, the default of 196609 is the result of combining the Ignore case, Ignore Kana, and Ignore width options.
傳回比較樣式。Returns the comparison style.

所有的二進位定序皆傳回 0。Returns 0 for all binary collations.

基底資料型別: intBase data type: int
版本Edition 資料庫版本或服務層。The database edition or service tier. 適用於: Azure SQL DatabaseAzure SQL DatabaseSQL 資料倉儲SQL Data WarehouseApplies to: Azure SQL DatabaseAzure SQL Database, SQL 資料倉儲SQL Data Warehouse.



Web = Web Edition 資料庫Web = Web Edition Database

Business = Business Edition 資料庫Business = Business Edition Database

BasicBasic

StandardStandard

PremiumPremium

系統 (適用於 master 資料庫)System (for master database)

NULL = 資料庫未啟動。NULL = Database is not started.

基底資料型別: nvarchar(64)Base data type: nvarchar(64)
IsAnsiNullDefaultIsAnsiNullDefault 資料庫遵照允許 Null 值的 ISO 規則。Database follows ISO rules for allowing null values. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAnsiNullsEnabledIsAnsiNullsEnabled 所有對於 Null 的比較,都會得出「未知」。All comparisons to a null evaluate to unknown. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAnsiPaddingEnabledIsAnsiPaddingEnabled 字串在進行比較或插入處理之前,先填補至相同的長度。Strings are padded to the same length before comparison or insert. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAnsiWarningsEnabledIsAnsiWarningsEnabled 當發生標準錯誤狀況時,會發出錯誤或警告訊息。Error or warning messages are issued when standard error conditions occur. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsArithmeticAbortEnabledIsArithmeticAbortEnabled 在查詢執行期間,當發生溢位或除以零的錯誤時,會停止查詢。Queries are ended when an overflow or divide-by-zero error occurs during query execution. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAutoCloseIsAutoClose 在最後一個使用者結束之後,資料庫完整關機並釋出資源。Database shuts down cleanly and frees resources after the last user exits. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAutoCreateStatisticsIsAutoCreateStatistics 查詢最佳化工具會視需要建立單一資料行統計資料來改善查詢效能。Query optimizer creates single-column statistics, as required, to improve query performance. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAutoCreateStatisticsIncrementalIsAutoCreateStatisticsIncremental 自動建立的單一資料行統計資料會累加 (如果可能)。Auto created single-column statistics are incremental when possible. 適用於SQL Server 2014SQL Server 2014SQL Server 2017SQL Server 2017Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017.



1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAutoShrinkIsAutoShrink 資料庫檔案是自動定期壓縮的候選項。Database files are candidates for automatic periodic shrinking. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsAutoUpdateStatisticsIsAutoUpdateStatistics 當查詢使用現有的統計資料而且這些統計資料可能已過期時,查詢最佳化工具就會更新這些統計資料。Query optimizer updates existing statistics when they are used by a query and might be out-of-date. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsCloneIsClone 使用者資料庫的結構描述和統計資料只複製資料庫。Database is a schema and statistics only copy of a user database. 適用於: SQL Server 2014SQL Server 2014 Service Pack 2。Applies to: SQL Server 2014SQL Server 2014 Service Pack 2.



1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsCloseCursorsOnCommitEnabledIsCloseCursorsOnCommitEnabled 關閉認可交易時在開啟狀態的資料指標。Cursors that are open when a transaction is committed are closed. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsFulltextEnabledIsFulltextEnabled 資料庫已啟用全文檢索和語意索引。Database is enabled for full-text and semantic indexing. 適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.



1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int

注意:這個屬性的值沒有任何作用。Note: The value of this property has no effect. 使用者資料庫一定會啟用全文檢索搜尋。User databases are always enabled for full-text search. 未來的 SQL ServerSQL Server 版本將移除這個資料行。This column will be removed in a future release of SQL ServerSQL Server. 請勿在新的開發工作中使用此資料行,並且儘速修改目前使用任何一個資料行的應用程式。Do not use this column in new development work, and modify applications that currently use any of these columns as soon as possible.
IsInStandByIsInStandBy 資料庫在線上唯讀,允許還原記錄。Database is online as read-only, with restore log allowed. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsLocalCursorsDefaultIsLocalCursorsDefault 資料指標宣告預設為 LOCAL。Cursor declarations default to LOCAL. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsMemoryOptimizedElevateToSnapshotEnabledIsMemoryOptimizedElevateToSnapshotEnabled 當工作階段設定 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. 適用於SQL Server 2014SQL Server 2014SQL Server 2017SQL Server 2017Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017.



1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

基底資料型別: intBase data type: int
IsMergePublishedIsMergePublished 可以為合併式複寫發行的資料庫資料表 (若已安裝複寫功能)。The tables of a database can be published for merge replication, if replication is installed. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsNullConcatIsNullConcat Null 串連運算元產生 NULL。Null concatenation operand yields NULL. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsNumericRoundAbortEnabledIsNumericRoundAbortEnabled 當運算式發生遺失有效位數的情形時,則產生錯誤。Errors are generated when loss of precision occurs in expressions. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsParameterizationForcedIsParameterizationForced PARAMETERIZATION 資料庫 SET 選項是 FORCED。PARAMETERIZATION database SET option is FORCED. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid
IsQuotedIdentifiersEnabledIsQuotedIdentifiersEnabled 識別碼可以使用雙引號。Double quotation marks can be used on identifiers. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsPublishedIsPublished 可以為快照集或異動複寫發行的資料庫資料表 (若已安裝複寫功能)。The tables of the database can be published for snapshot or transactional replication, if replication is installed. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsRecursiveTriggersEnabledIsRecursiveTriggersEnabled 啟用觸發程序的遞迴引發。Recursive firing of triggers is enabled. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsSubscribedIsSubscribed 將資料庫訂閱到發行集中。Database is subscribed to a publication. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsSyncWithBackupIsSyncWithBackup 該資料庫是已發行的資料庫或散發資料庫,並且能在不干擾異動複寫的情況下被還原。The database is either a published database or a distribution database, and can be restored without disrupting transactional replication. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsTornPageDetectionEnabledIsTornPageDetectionEnabled SQL Server Database EngineSQL Server Database Engine 偵測到因為斷電或其他系統失效所造成的不完全 I/O 作業。The SQL Server Database EngineSQL Server Database Engine detects incomplete I/O operations caused by power failures or other system outages. 1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效NULL = Input not valid

基底資料型別: intBase data type: int
IsXTPSupportedIsXTPSupported 指出是否在資料庫支援記憶體中 OLTP,也就是,建立和使用的記憶體最佳化資料表和原生編譯的模組。Indicates whether the database supports In-Memory OLTP, i.e., creating and using memory-optimized tables and natively compiled modules.

特定 SQL ServerSQL Server:Specific to SQL ServerSQL Server:

IsXTPSupported 是獨立於任何 MEMORY_OPTIMIZED_DATA 檔案群組中,存在才能建立記憶體中 OLTP 物件。IsXTPSupported is independent of the existence of any MEMORY_OPTIMIZED_DATA filegroup, which is required for creating In-Memory OLTP objects.
適用於: SQL ServerSQL Server ( SQL Server 2014SQL Server 2014透過 SQL Server 2017SQL Server 2017), SQL DatabaseSQL DatabaseApplies to: SQL ServerSQL Server ( SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017), SQL DatabaseSQL Database.

適用於: Azure SQL DatabaseAzure SQL DatabaseSQL ServerSQL Server啟動 SQL Server 2016SQL Server 2016Applies to: Azure SQL DatabaseAzure SQL Database and SQL ServerSQL Server starting SQL Server 2016SQL Server 2016.



1 = TRUE1 = TRUE

0 = FALSE0 = FALSE

NULL = 輸入無效,發生錯誤,或不適用NULL = Input not valid, an error, or not applicable

基底資料型別: intBase data type: int
LCIDLCID Windows 的定序地區設定識別碼 (LCID)。The Windows locale identifier (LCID) of the collation. LCID 值 (十進位格式)。LCID value (in decimal format).

基底資料型別: intBase data type: int
MaxSizeInBytesMaxSizeInBytes 資料庫的大小上限 (以位元組為單位)。Maximum database size in bytes. 適用於: Azure SQL DatabaseAzure SQL DatabaseSQL 資料倉儲SQL Data WarehouseApplies to: Azure SQL DatabaseAzure SQL Database, SQL 資料倉儲SQL Data Warehouse.



10737418241073741824

53687091205368709120

1073741824010737418240

2147483648021474836480

3221225472032212254720

4294967296042949672960

5368709120053687091200

NULL = 資料庫未啟動NULL = Database is not started

基底資料型別: bigintBase data type: bigint
復原Recovery 資料庫的復原模式。Recovery model for the database. FULL = 完全復原模式FULL = Full recovery model

BULK_LOGGED = 大量記錄模式BULK_LOGGED = Bulk logged model

SIMPLE = 簡單復原模式SIMPLE = Simple recovery model

基底資料型別: nvarchar (128)Base data type: nvarchar(128)
ServiceObjectiveServiceObjective 描述在資料庫的效能層級 SQL DatabaseSQL DatabaseSQL 資料倉儲SQL Data WarehouseDescribes the performance level of the database in SQL DatabaseSQL Database or SQL 資料倉儲SQL Data Warehouse. 可以是下列其中一項:Can be one of the following:

Null: 資料庫未啟動Null: database not started

共用 (適用於 Web/Business 版本)Shared (for Web/Business editions)

BasicBasic

S0S0

S1S1

S2S2

S3S3

P1P1

P2P2

P3P3

ElasticPoolElasticPool

系統 (適用於 master 資料庫)System (for master DB)

基底資料型別: nvarchar (32)Base data type: nvarchar(32)
ServiceObjectiveIdServiceObjectiveId SQL DatabaseSQL Database 中服務目標的識別碼。The id of the service objective in SQL DatabaseSQL Database. uniqueidentifier識別服務目標。uniqueidentifier that identifies the service objective.
SQLSortOrderSQLSortOrder 舊版 SQL Server 中所支援的 SQL ServerSQL Server 排序順序識別碼。 SQL ServerSQL Server sort order ID supported in earlier versions of SQL Server. 0 = 資料庫使用 Windows 定序0 = Database is using Windows collation

>0 = SQL ServerSQL Server 排序順序識別碼>0 = SQL ServerSQL Server sort order ID

NULL = 輸入無效或未啟動資料庫。NULL = Input not valid or database is not started

基底資料型別: tinyintBase data type: tinyint
狀態Status 資料庫狀態。Database status. ONLINE = 資料庫可用於查詢。ONLINE = Database is available for query.

注意:可能時資料庫正在開啟,無法傳回尚未復原線上的狀態。Note: The ONLINE status may be returned while the database is being opened and is not yet recovered. 若要識別資料庫可接受連接時,查詢的定序屬性DATABASEPROPERTYEXTo identify when a database can accept connections, query the Collation property of DATABASEPROPERTYEX. 當資料庫定序傳回非 Null 值時,表示資料庫可接受連接。The database can accept connections when the database collation returns a non-null value. 對於 Alwayson 資料庫,查詢 sys.dm_hadr_database_replica_states database_state 或 database_state_desc 資料的行。For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.

OFFLINE = 資料庫明確離線。OFFLINE = Database was explicitly taken offline.

RESTORING = 正在還原資料庫。RESTORING = Database is being restored.

RECOVERING = 資料庫復原中,無法進行查詢。RECOVERING = Database is recovering and not yet ready for queries.

SUSPECT = 資料庫未復原。SUSPECT = Database did not recover.

EMERGENCY = 資料庫處於緊急、唯讀的狀態。EMERGENCY = Database is in an emergency, read-only state. 存取限於系統管理員 (sysadmin) 成員Access is restricted to sysadmin members

基底資料型別: nvarchar (128)Base data type: nvarchar(128)
UpdateabilityUpdateability 指出資料是否可以修改。Indicates whether data can be modified. READ_ONLY = 資料可以讀取,但無法修改。READ_ONLY = Data can be read but not modified.

READ_WRITE = 資料可以讀取及修改。READ_WRITE = Data can be read and modified.

基底資料型別: nvarchar (128)Base data type: nvarchar(128)
UserAccessUserAccess 指定那些使用者可以存取資料庫。Indicates which users can access the database. SINGLE_USER = 一次只有一個 db_owner、資料庫建立者 (dbcreator) 或系統管理員 (sysadmin) 使用者SINGLE_USER = Only one db_owner, dbcreator, or sysadmin user at a time

RESTRICTED_USER = 只有 db_owner、資料庫建立者 (dbcreator) 和系統管理員 (sysadmin) 角色RESTRICTED_USER = Only members of db_owner, dbcreator, and sysadmin roles

MULTI_USER = 所有使用者MULTI_USER = All users

基底資料型別: nvarchar (128)Base data type: nvarchar(128)
VersionVersion 建立資料庫所用 SQL ServerSQL Server 程式碼的內部版本號碼。Internal version number of the SQL ServerSQL Server code with which the database was created. 僅供參考之用。Identified for informational purposes only. 不支援。Not supported. 我們無法保證未來的相容性。Future compatibility is not guaranteed. 版本號碼 = 資料庫是開啟的。Version number = Database is open.

NULL = 資料庫未啟動。NULL = Database is not started.

基底資料型別: intBase data type: int

傳回型別Return types

sql_variantsql_variant

例外狀況Exceptions

當發生錯誤,或呼叫端沒有檢視物件的權限時,便會傳回 NULL。Returns NULL on error or if a caller does not have permission to view the object.

SQL ServerSQL Server 中,使用者只能檢視使用者擁有或被授與某些權限之安全性實體的中繼資料。In SQL ServerSQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. 這表示發出中繼資料的內建函數 (例如,OBJECT_ID) 會在使用者不具有該物件任何權限時傳回 NULL。This means that metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object. 如需相關資訊,請參閱 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

備註Remarks

DATABASEPROPERTYEX 一次只傳回一個屬性設定。DATABASEPROPERTYEX returns only one property setting at a time. 若要顯示多個屬性設定,請使用sys.databases目錄檢視。To display multiple property settings, use the sys.databases catalog view.

範例Examples

A.A. 擷取 AUTO_SHRINK 資料庫選項的狀態Retrieving the status of the AUTO_SHRINK database option

下列範例會傳回 AdventureWorks 資料庫 AUTO_SHRINK 資料庫選項的狀態。The following example returns the status of the AUTO_SHRINK database option for the AdventureWorks database.

SELECT DATABASEPROPERTYEX('AdventureWorks2014', 'IsAutoShrink');  

以下為結果集:Here is the result set. 這表示 AUTO_SHRINK 是關閉狀態。 This indicates that AUTO_SHRINK is off.

------------------  
0  

B.B. 擷取資料庫的預設定序Retrieving the default collation for a database

下列範例會傳回的數個屬性AdventureWorks資料庫。The following example returns several attributes of the AdventureWorks database.

SELECT   
    DATABASEPROPERTYEX('AdventureWorks2014', 'Collation') AS Collation,  
    DATABASEPROPERTYEX('AdventureWorks2014', 'Edition') AS Edition,  
    DATABASEPROPERTYEX('AdventureWorks2014', 'ServiceObjective') AS ServiceObjective,  
    DATABASEPROPERTYEX('AdventureWorks2014', 'MaxSizeInBytes') AS MaxSizeInBytes  

以下為結果集:Here is the result set.

Collation                     Edition        ServiceObjective  MaxSizeInBytes  
----------------------------  -------------  ----------------  --------------  
SQL_Latin1_General_CP1_CI_AS  DataWarehouse  DW1000            5368709120  

另請參閱See also

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)
資料庫狀態Database States
sys.databases (Transact-SQL)sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)sys.database_files (Transact-SQL)
SERVERPROPERTY (Transact-SQL)SERVERPROPERTY (Transact-SQL)