databasepropertyex-transact-sql.md

title	DATABASEPROPERTYEX (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	04/23/2018
ms.prod	sql
ms.prod_service	database-engine, sql-database, sql-data-warehouse, pdw
ms.component	t-sql|functions
ms.reviewer
ms.suite	sql
ms.technology	t-sql
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	DATABASEPROPERTYEX DATABASEPROPERTYEX_TSQL
dev_langs	TSQL
helpviewer_keywords	DATABASEPROPERTYEX function displaying database properties database properties [SQL Server]
ms.assetid	8a9e0ffb-28b5-4640-95b2-a54e3e5ad941
caps.latest.revision	84
author	edmacauley
ms.author	edmaca
manager	craigg
monikerRange	>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions

DATABASEPROPERTYEX (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

For a specified database in [!INCLUDEssNoVersion], this function returns the current setting of the specified database option or property.

Transact-SQL Syntax Conventions

Syntax

DATABASEPROPERTYEX ( database , property )  

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 [!INCLUDEssSDS], 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 has not 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 is not 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: [!INCLUDEssSDSfull], [!INCLUDEssSDW]. General Purpose Business Critical Basic Standard Premium System (for master database) NULL: Database is not 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: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. 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: [!INCLUDEssSQL14] SP2 through [!INCLUDEssCurrent]. 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
IsFulltextEnabled	Database is enabled for full-text and semantic indexing.	Applies to: [!INCLUDEssKatmai] through [!INCLUDEssCurrent]. 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 [!INCLUDEssNoVersion] 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: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. 1: TRUE 0: FALSE Base data type: int
IsMergePublished	[!INCLUDEssNoVersion] 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, [!INCLUDEssNoVersion] 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 [!INCLUDEssDEnoversion] 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 [!INCLUDEssSQL15] SP2. 1: TRUE 0: FALSE NULL: Invalid input Base data type: int
IsXTPSupported	Indicates whether the database supports In-Memory OLTP, i.e., creation and use of memory-optimized tables and natively compiled modules. Specific to [!INCLUDEssNoVersion]: IsXTPSupported is independent of the existence of any MEMORY_OPTIMIZED_DATA filegroup, which is required for creating In-Memory OLTP objects.	Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] through [!INCLUDEssCurrent]), and [!INCLUDEssSDSfull]. 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.	Applies to: Starting with [!INCLUDEssSQL15] SP2. 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: [!INCLUDEssSDSfull], [!INCLUDEssSDW]. 1073741824 5368709120 10737418240 21474836480 32212254720 42949672960 53687091200 NULL: Database is not 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 [!INCLUDEsqldbesa] or [!INCLUDEssSDW].	One of the following: 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 [!INCLUDEsqldbesa].	uniqueidentifier that identifies the service objective.
SQLSortOrder	[!INCLUDEssNoVersion] sort order ID supported in earlier versions of SQL Server.	0: Database uses Windows collation >0: [!INCLUDEssNoVersion] sort order ID NULL: Invalid input, or database has not started Base data type: tinyint
Status	Database status.	ONLINE: Database is available for query. Note: The ONLINE status may be returned while the database opens and has not yet recovered. To identify when a database can accept connections, query the Collation property of DATABASEPROPERTYEX. The 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 is not yet ready for queries. SUSPECT: Database did not 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 [!INCLUDEssNoVersion] code with which the database was created. [!INCLUDEssInternalOnly]	Version number: Database is open. NULL: Database has not started. Base data type: int

Return types

sql_variant

Exceptions

Returns NULL on error, or if a caller does not have permission to view the object.

In [!INCLUDEssNoVersion], a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This 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');  

[!INCLUDEssResult] 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  

[!INCLUDEssResult]

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

See also

ALTER DATABASE (Transact-SQL)
Database States
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
SERVERPROPERTY (Transact-SQL)