| title | DATABASEPROPERTYEX (Transact-SQL) | |||
|---|---|---|---|---|
| description | DATABASEPROPERTYEX returns the current setting of the specified database option or property. | |||
| author | markingmyname | |||
| ms.author | maghan | |||
| ms.reviewer | wiassaf, randolphwest | |||
| ms.date | 03/11/2026 | |||
| ms.service | sql | |||
| ms.subservice | t-sql | |||
| ms.topic | reference | |||
| ms.custom |
|
|||
| f1_keywords |
|
|||
| helpviewer_keywords |
|
|||
| dev_langs |
|
|||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricsqldb]
For a specified database in [!INCLUDE ssNoVersion], the DATABASEPROPERTYEX function returns the current setting of the specified database option or property.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
DATABASEPROPERTYEX ( database , property )
An expression specifying the name of the database for which DATABASEPROPERTYEX returns the named property information. database has an nvarchar(128) data type.
For [!INCLUDE ssSDS], DATABASEPROPERTYEX requires the name of the current database. It returns NULL for all properties if given a different database name.
An expression specifying the name of the database property to return. property has a varchar(128) data type, and supports one of the values in this table:
Note
If the database hasn't yet started, calls to DATABASEPROPERTYEX return NULL if DATABASEPROPERTYEX retrieves those values by direct database access, instead of retrieval from metadata. A database with AUTO_CLOSE set to ON, or otherwise offline, is defined as 'not started.'
| Property | Description | Value returned |
|---|---|---|
CollationData type: nvarchar(128) |
Default collation name for the database. | Collation name. If NULL, the database isn't started. |
ComparisonStyleData type: int |
The Windows comparison style of the collation. Use the following style values to build a bitmap for the finished ComparisonStyle value:- 1: Ignore case- 2: Ignore accent- 65536: Ignore kana- 131072: Ignore widthFor example, the default of 196609 is the result of combining the ignore case, ignore kana, and ignore width options. |
Returns the comparison style. Returns 0 for all binary collations. |
EditionData type: nvarchar(64) |
The database edition or service tier. | - General Purpose- Business Critical- Basic- Standard- Premium- System (for master database)- FabricSQLDB: [!INCLUDE fabric-sqldb]- NULL: Database isn't started.Applies to: [!INCLUDE ssazure-sqldb], [!INCLUDE fabric-sqldb], [!INCLUDE ssazuresynapse-md]. |
IsAnsiNullDefaultData type: int |
Database follows ISO rules for allowing NULL values. |
- 1: True- 0: False- NULL: Invalid input |
IsAnsiNullsEnabledData type: int |
All comparisons to a NULL evaluate to unknown. |
- 1: True- 0: False- NULL: Invalid input |
IsAnsiPaddingEnabledData type: int |
Strings are padded to the same length before comparison or insert. | - 1: True- 0: False- NULL: Invalid input |
IsAnsiWarningsEnabledData type: int |
SQL Server issues error or warning messages when standard error conditions occur. | - 1: True- 0: False- NULL: Invalid input |
IsArithmeticAbortEnabledData type: int |
Queries end when an overflow or divide-by-zero error occurs during query execution. | - 1: True- 0: False- NULL: Invalid input |
IsAutoCloseData type: int |
Database shuts down cleanly and frees resources after the last user exits. | - 1: True- 0: False- NULL: Invalid input |
IsAutoCreateStatisticsData type: int |
Query optimizer creates single-column statistics, as required, to improve query performance. | - 1: True- 0: False- NULL: Invalid input |
IsAutoCreateStatisticsIncrementalData type: int |
Auto-created single column statistics are incremental when possible. | - 1: True- 0: False- NULL: Invalid inputApplies to: [!INCLUDE ssSQL14] and later versions. |
IsAutomaticIndexCompactionOnData type: int |
Automatic index compaction is enabled for the database. | - 1: True- 0: False- NULL: Not availableApplies to: [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md]AUTD, and [!INCLUDE fabric-sqldb]. |
IsAutoShrinkData type: int |
Database files are candidates for automatic periodic shrinking. | - 1: True- 0: False- NULL: Invalid input |
IsAutoUpdateStatisticsData type: int |
When a query uses potentially out-of-date existing statistics, the query optimizer updates those statistics. | - 1: True- 0: False- NULL: Input not valid |
IsCloneData type: int |
Database is a schema- and statistics-only copy of a user database created with DBCC CLONEDATABASE. |
- 1: True- 0: False- NULL: Invalid inputApplies to: [!INCLUDE ssSQL14] SP2 and later versions. |
IsCloseCursorsOnCommitEnabledData type: int |
When a transaction commits, all open cursors are closed. | - 1: True- 0: False- NULL: Invalid input |
IsDatabaseSuspendedForSnapshotBackupData type: int |
Database is suspended. | - 1: True- 0: False- NULL: Invalid input |
IsFulltextEnabledData type: int |
Database is enabled for full-text and semantic indexing. | - 1: True- 0: False- NULL: Input not validApplies to: [!INCLUDE sql2008-md] and later versions. Note: The value of this property now has no effect. User databases are always enabled for full-text search. A future release of [!INCLUDE ssNoVersion] will remove this property. Don't use this property in new development work, and modify applications that currently use this property as soon as possible. |
IsInStandByData type: int |
Database is online as read-only, with restore log allowed. | - 1: True- 0: False- NULL: Invalid input |
IsLocalCursorsDefaultData type: int |
Cursor declarations default to LOCAL. |
- 1: True- 0: False- NULL: Invalid input |
IsMemoryOptimizedElevateToSnapshotEnabledData type: int |
Memory-optimized tables are accessed using SNAPSHOT isolation, when the session setting TRANSACTION ISOLATION LEVEL is set to READ COMMITTED, READ UNCOMMITTED, or a lower isolation level. |
- 1: True- 0: FalseApplies to: [!INCLUDE ssSQL14] and later versions. |
IsMergePublishedData type: int |
[!INCLUDE ssNoVersion] supports database table publication for merge replication, if replication is installed. | - 1: True- 0: False- NULL: Invalid input |
IsNullConcatData type: int |
Null concatenation operand yields NULL. |
- 1: True- 0: False- NULL: Invalid input |
IsNumericRoundAbortEnabledData type: int |
Errors are generated when a loss of precision occurs in expressions. | - 1: True- 0: False- NULL: Invalid input |
IsOptimizedLockingOnData type: int |
Optimized locking is enabled for the database. | - 1: True- 0: False- NULL: Not availableApplies to: [!INCLUDE sssql25-md] and later versions, [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md]AUTD, and [!INCLUDE fabric-sqldb]. |
IsParameterizationForcedData type: int |
PARAMETERIZATION database SET option is FORCED. |
- 1: True- 0: False- NULL: Invalid input |
IsQuotedIdentifiersEnabledData type: int |
Double quotation marks on identifiers are allowed. | - 1: True- 0: False- NULL: Invalid input |
IsPublishedData type: int |
If replication is installed, [!INCLUDE ssNoVersion] supports database table publication for snapshot or transactional replication. | - 1: True- 0: False- NULL: Invalid input |
IsRecursiveTriggersEnabledData type: int |
Recursive firing of triggers is enabled. | - 1: True- 0: False- NULL: Invalid input |
IsSubscribedData type: int |
Database is subscribed to a publication. | - 1: True- 0: False- NULL: Invalid input |
IsSyncWithBackupData type: int |
The database is either a published database or a distribution database, and it supports a restore that doesn't disrupt transactional replication. | - 1: True- 0: False- NULL: Invalid input |
IsTornPageDetectionEnabledData type: int |
The [!INCLUDE ssDEnoversion] detects incomplete I/O operations caused by power failures or other system outages. | - 1: True- 0: False- NULL: Invalid input |
IsVerifiedCloneData type: int |
Database is a schema- and statistics- only copy of a user database, created using the WITH VERIFY_CLONEDB option of DBCC CLONEDATABASE. |
- 1: True- 0: False- NULL: Invalid inputApplies to: [!INCLUDE sssql16-md] SP2 and later versions. |
IsXTPSupportedData type: int |
Indicates whether the database supports In-Memory OLTP. For example, creation and use of memory-optimized tables and natively compiled modules. Specific to [!INCLUDE ssNoVersion]: IsXTPSupported is independent of the existence of any MEMORY_OPTIMIZED_DATA filegroup, which is required for creating In-Memory OLTP objects. |
- 1: True- 0: False- NULL: Invalid input, an error, or not applicableApplies to: [!INCLUDE sssql16-md] and later versions, and [!INCLUDE ssazure-sqldb]. |
LastGoodCheckDbTimeData type: datetime |
The date and time of the last successful DBCC CHECKDB that ran on the specified database. If DBCC CHECKDB hasn't been run on a database, 1900-01-01 00:00:00.000 is returned. For databases that are part of an availability group, LastGoodCheckDbTime returns the date and time of the last successful DBCC CHECKDB that ran on the primary replica, regardless of which replica you run the command from. |
NULL: Invalid inputApplies to: [!INCLUDE sssql16-md] SP2, [!INCLUDE sssql17] CU9, [!INCLUDE sssql19] and later versions, Azure SQL Database, and [!INCLUDE fabric-sqldb]. |
LCIDData type: int |
The collation Windows locale identifier (LCID). | LCID value (in decimal format). |
MaxSizeInBytesData type: bigint |
Maximum database size, in bytes. | - Azure SQL Database and Azure Synapse Analytics: Value is based on SLO unless extra storage has been purchased. - vCore: Value is in 1GB increments up to max size. - NULL: Database isn't startedApplies to: [!INCLUDE ssazure-sqldb], [!INCLUDE fabric-sqldb], and [!INCLUDE ssazuresynapse-md]. |
RecoveryData type: nvarchar(128) |
Database recovery model. | - FULL: Full recovery model- BULK_LOGGED: Bulk logged model- SIMPLE: Simple recovery model |
ServiceObjectiveData type: nvarchar(32) |
Describes the performance level of the database in [!INCLUDE sssds], [!INCLUDE fabric-sqldb], or [!INCLUDE ssazuresynapse-md]. | One of the following values: - NULL: database not started- Shared (for Web/Business editions)- Basic- S0- S1- S2- S3- P1- P2- P3- ElasticPool- System (for master database)- FabricSQLDB: [!INCLUDE fabric-sqldb] |
ServiceObjectiveIdData type: uniqueidentifier |
The ID of the service objective in [!INCLUDE sssds]. | ID of the service objective. |
SQLSortOrderData type: tinyint |
[!INCLUDE ssNoVersion] sort order ID supported in earlier versions of SQL Server. | - 0: Database uses Windows collation- >0: [!INCLUDE ssNoVersion] sort order ID- NULL: Invalid input, or database hasn't started |
StatusData type: nvarchar(128) |
Database status. | ONLINE: Database is available for query.Note: The function might return a status of ONLINE while the database opens and hasn't yet recovered. To identify if an ONLINE database can accept connections, query the Collation property of DATABASEPROPERTYEX. The ONLINE database can accept connections when the database collation returns a non-null value. For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.- OFFLINE: Database was explicitly taken offline.- RESTORING: Database restore has started.- RECOVERING: Database recovery has started and the database isn't yet ready for queries.- SUSPECT: Database didn't recover.- EMERGENCY: Database is in an emergency, read-only state. Access is restricted to sysadmin members |
UpdateabilityData type: nvarchar(128) |
Indicates whether data can be modified. | READ_ONLY: Database supports data reads but not data modifications.- READ_WRITE: Database supports data reads and modifications. |
UserAccessData type: nvarchar(128) |
Indicates which users can access the database. | SINGLE_USER: Only one db_owner, dbcreator, or sysadmin user at a time- RESTRICTED_USER: Only members of db_owner, dbcreator, or sysadmin fixed server roles- MULTI_USER: All users |
VersionData type: int |
Internal version number of the [!INCLUDE ssNoVersion] code with which the database was created. [!INCLUDE ssInternalOnly] | - Version number: Database is open. - NULL: Database hasn't started. |
ReplicaIDData type: nvarchar(128) |
The replica ID of a connected hyperscale database/replica. | Only returns the replica ID of a connected Hyperscale database/replica. To learn more about replica types, see Hyperscale secondary replicas. - NULL: Not a hyperscale database, or the database isn't started.Applies to: Azure SQL Database Hyperscale. |
sql_variant
Returns NULL on error, or if a caller doesn't have permission to view the object.
In [!INCLUDE ssNoVersion], a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This rule means that metadata-emitting, built-in functions such as OBJECT_ID can return NULL if the user has no permissions on the object. For more information, see Metadata visibility configuration.
DATABASEPROPERTYEX returns only one property setting at a time. To display multiple property settings, use the sys.databases catalog view.
[!INCLUDE article-uses-adventureworks]
This example returns the status of the AUTO_SHRINK database option for the AdventureWorks database.
SELECT DATABASEPROPERTYEX('AdventureWorks2022', 'IsAutoShrink');[!INCLUDE ssResult] This indicates that AUTO_SHRINK is off.
0
This example returns several attributes of the AdventureWorks database.
SELECT DATABASEPROPERTYEX('AdventureWorks2022', 'Collation') AS Collation,
DATABASEPROPERTYEX('AdventureWorks2022', 'Edition') AS Edition,
DATABASEPROPERTYEX('AdventureWorks2022', 'ServiceObjective') AS ServiceObjective,
DATABASEPROPERTYEX('AdventureWorks2022', 'MaxSizeInBytes') AS MaxSizeInBytes;[!INCLUDE ssResult]
Collation Edition ServiceObjective MaxSizeInBytes
---------------------------- ------------- ---------------- --------------
SQL_Latin1_General_CP1_CI_AS DataWarehouse DW1000 5368709120
When you use the Azure SQL Database scale-out feature, you can verify whether you're connected to a read-only replica or not by running the following query in the context of your database. It returns READ_ONLY when you're connected to a read-only replica. This way, you can also identify when a query is running on a read-only replica.
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');