DATABASEPROPERTYEX (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
For a specified database in SQL Server, this function returns the current setting of the specified database option or property.
Transact-SQL syntax conventions
Syntax
DATABASEPROPERTYEX ( database , property )
Note
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
database
An expression specifying the name of the database for which DATABASEPROPERTYEX will return the named property information. database has an nvarchar(128) data type.
For SQL Database, DATABASEPROPERTYEX requires the name of the current database. It returns NULL for all properties if given a different database name.
property
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 will 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 |
|---|---|---|
| Collation | Default collation name for the database. | Collation name NULL: Database isn't started. Base data type: nvarchar(128) |
| ComparisonStyle | The Windows comparison style of the collation. Use the following style values to build a bitmap for the finished ComparisonStyle value: Ignore case: 1 Ignore accent: 2 Ignore kana: 65536 Ignore width: 131072 For 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. Base data type: int |
| Edition | The database edition or service tier. | Applies to: Azure SQL Database, Azure Synapse Analytics. General Purpose Business Critical Basic Standard Premium System (for master database) NULL: Database isn't started. Base data type: nvarchar(64) |
| IsAnsiNullDefault | Database follows ISO rules for allowing null values. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAnsiNullsEnabled | All comparisons to a null evaluate to unknown. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAnsiPaddingEnabled | Strings are padded to the same length before comparison or insert. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAnsiWarningsEnabled | SQL Server issues error or warning messages when standard error conditions occur. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsArithmeticAbortEnabled | Queries end when an overflow or divide-by-zero error occurs during query execution. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAutoClose | Database shuts down cleanly and frees resources after the last user exits. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAutoCreateStatistics | Query optimizer creates single-column statistics, as required, to improve query performance. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAutoCreateStatisticsIncremental | Auto-created single column statistics are incremental when possible. | Applies to: SQL Server 2014 (12.x) and later. 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAutoShrink | Database files are candidates for automatic periodic shrinking. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsAutoUpdateStatistics | When a query uses potentially out-of-date existing statistics, the query optimizer updates those statistics. | 1: TRUE 0: FALSE NULL: Input not valid Base data type: int |
| IsClone | Database is a schema- and statistics-only copy of a user database created with DBCC CLONEDATABASE. See Microsoft Support Article for more information. | Applies to: SQL Server 2014 (12.x) SP2 and later. 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsCloseCursorsOnCommitEnabled | When a transaction commits, all open cursors will close. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsDatabaseSuspendedForSnapshotBackup | Database is suspended. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsFulltextEnabled | Database is enabled for full-text and semantic indexing. | Applies to: SQL Server 2008 (10.0.x) and later. 1: TRUE 0: FALSE NULL: Input not valid Base data type: int Note: The value of this property now has no effect. User databases are always enabled for full-text search. A future release of SQL Server will remove this property. Do not use this property in new development work, and modify applications that currently use this property as soon as possible. |
| IsInStandBy | Database is online as read-only, with restore log allowed. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsLocalCursorsDefault | Cursor declarations default to LOCAL. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsMemoryOptimizedElevateToSnapshotEnabled | 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. | Applies to: SQL Server 2014 (12.x) and later. 1: TRUE 0: FALSE Base data type: int |
| IsMergePublished | SQL Server supports database table publication for merge replication, if replication is installed. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsNullConcat | Null concatenation operand yields NULL. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsNumericRoundAbortEnabled | Errors are generated when a loss of precision occurs in expressions. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsParameterizationForced | PARAMETERIZATION database SET option is FORCED. | 1: TRUE 0: FALSE NULL: Invalid input |
| IsQuotedIdentifiersEnabled | Double quotation marks on identifiers are allowed. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsPublished | If replication is installed, SQL Server supports database table publication for snapshot or transactional replication. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsRecursiveTriggersEnabled | Recursive firing of triggers is enabled. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsSubscribed | Database is subscribed to a publication. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsSyncWithBackup | The database is either a published database or a distribution database, and it supports a restore that will not disrupt transactional replication. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsTornPageDetectionEnabled | The SQL Server Database Engine detects incomplete I/O operations caused by power failures or other system outages. | 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsVerifiedClone | Database is a schema- and statistics- only copy of a user database, created using the WITH VERIFY_CLONEDB option of DBCC CLONEDATABASE. See this Microsoft Support Article for more information. | Applies to: Starting with SQL Server 2016 (13.x) SP2. 1: TRUE 0: FALSE NULL: Invalid input Base data type: int |
| IsXTPSupported | Indicates whether the database supports In-Memory OLTP. For example, creation and use of memory-optimized tables and natively compiled modules. Specific to SQL Server: IsXTPSupported is independent of the existence of any MEMORY_OPTIMIZED_DATA filegroup, which is required for creating In-Memory OLTP objects. |
Applies to: SQL Server (SQL Server 2016 (13.x) and later), and Azure SQL Database. 1: TRUE 0: FALSE NULL: Invalid input, an error, or not applicable Base data type: int |
| LastGoodCheckDbTime | The date and time of the last successful DBCC CHECKDB that ran on the specified database.1 If DBCC CHECKDB hasn't been run on a database, 1900-01-01 00:00:00.000 is returned. | Applies to: SQL Server 2016 (13.x) beginning with SP2. SQL Server 2017 (14.x) beginning with CU9. SQL Server 2019 (15.x) or later. Azure SQL Database. A datetime value NULL: Invalid input Base data type: datetime |
| LCID | The collation Windows locale identifier (LCID). | LCID value (in decimal format). Base data type: int |
| MaxSizeInBytes | Maximum database size, in bytes. | Applies to: Azure SQL Database, Azure Synapse Analytics. Azure SQL Database and Azure Synapse Analytics – Value is based on SLO unless additional storage has been purchased. vCore – Value is in 1GB increments up to max size. NULL: Database isn't started Base data type: bigint |
| Recovery | Database recovery model | FULL: Full recovery model BULK_LOGGED: Bulk logged model SIMPLE: Simple recovery model Base data type: nvarchar(128) |
| ServiceObjective | Describes the performance level of the database in SQL Database or Azure Synapse Analytics. | 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 DB) Base data type: nvarchar(32) |
| ServiceObjectiveId | The ID of the service objective in SQL Database. | uniqueidentifier that identifies the service objective. |
| SQLSortOrder | SQL Server sort order ID supported in earlier versions of SQL Server. | 0: Database uses Windows collation >0: SQL Server sort order ID NULL: Invalid input, or database hasn't started Base data type: tinyint |
| Status | Database status. | ONLINE: Database is available for query. Note: The function may 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 Base data type: nvarchar(128) |
| Updateability | Indicates whether data can be modified. | READ_ONLY: Database supports data reads but not data modifications. READ_WRITE: Database supports data reads and modifications. Base data type: nvarchar(128) |
| UserAccess | 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 roles MULTI_USER: All users Base data type: nvarchar(128) |
| Version | Internal version number of the SQL 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: Database hasn't started. Base data type: int |
Note
1 For databases that are part of an Availability Group, LastGoodCheckDbTime will return 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.
Return types
sql_variant
Exceptions
Returns NULL on error, or if a caller does not have permission to view the object.
In SQL Server, 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 may return NULL if the user has no permissions on the object. See Metadata Visibility Configuration for more information.
Remarks
DATABASEPROPERTYEX returns only one property setting at a time. To display multiple property settings, use the sys.databases catalog view.
Examples
A. Retrieving the status of the AUTO_SHRINK database option
This example returns the status of the AUTO_SHRINK database option for the AdventureWorks database.
SELECT DATABASEPROPERTYEX('AdventureWorks2014', 'IsAutoShrink');
Here is the result set. This indicates that AUTO_SHRINK is off.
------------------
0
B. Retrieving the default collation for a database
This 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
C. Use DATABASEPROPERTYEX to verify connection to replica
When using Azure SQL Database read the 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 will return 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');
See also
ALTER DATABASE (Transact-SQL)
Database States
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
SERVERPROPERTY (Transact-SQL)