| 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 |
|
|||
| dev_langs |
|
|||
| helpviewer_keywords |
|
|||
| 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 |
[!INCLUDEtsql-appliesto-ss2008-all-md]
Returns the current setting of the specified database option or property for the specified database in [!INCLUDEssNoVersion].
Transact-SQL Syntax Conventions
DATABASEPROPERTYEX ( database , property ) database
Is an expression that represents the name of the database for which to return the named property information. database is nvarchar(128).
For [!INCLUDEssSDS], must be the name of the current database. 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. property is varchar(128), and can be one of the following values. The return type is sql_variant. The following table shows the base data type for each property value.
Note
If the database is not started, properties that the [!INCLUDEssNoVersion] retrieves by accessing the database directly instead of retrieving the value from metadata will return NULL. That is, if the database has AUTO_CLOSE set to ON, or the database is otherwise offline.
| 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. ComparisonStyle is a bitmap that is calculated by using the following values for the possible styles. 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 = Input not valid Base data type: int |
| IsAnsiNullsEnabled | All comparisons to a null evaluate to unknown. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsAnsiPaddingEnabled | Strings are padded to the same length before comparison or insert. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsAnsiWarningsEnabled | Error or warning messages are issued when standard error conditions occur. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsArithmeticAbortEnabled | Queries are ended when an overflow or divide-by-zero error occurs during query execution. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsAutoClose | Database shuts down cleanly and frees resources after the last user exits. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsAutoCreateStatistics | Query optimizer creates single-column statistics, as required, to improve query performance. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsAutoCreateStatisticsIncremental | Auto created single-column statistics are incremental when possible. | Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsAutoShrink | Database files are candidates for automatic periodic shrinking. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsAutoUpdateStatistics | Query optimizer updates existing statistics when they are used by a query and might be out-of-date. | 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. For more information, see this Microsoft Support Article. | Applies to: [!INCLUDEssSQL14] SP2 through [!INCLUDEssCurrent]. 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsCloseCursorsOnCommitEnabled | Cursors that are open when a transaction is committed are closed. | 1 = TRUE 0 = FALSE NULL = Input not valid 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 has no effect. User databases are always enabled for full-text search. This column will be removed in a future release of [!INCLUDEssNoVersion]. Do not use this column in new development work, and modify applications that currently use any of these columns as soon as possible. |
| IsInStandBy | Database is online as read-only, with restore log allowed. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsLocalCursorsDefault | Cursor declarations default to LOCAL. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsMemoryOptimizedElevateToSnapshotEnabled | 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. | Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. 1 = TRUE 0 = FALSE Base data type: int |
| IsMergePublished | The tables of a database can be published for merge replication, if replication is installed. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsNullConcat | Null concatenation operand yields NULL. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsNumericRoundAbortEnabled | Errors are generated when loss of precision occurs in expressions. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsParameterizationForced | PARAMETERIZATION database SET option is FORCED. | 1 = TRUE 0 = FALSE NULL = Input not valid |
| IsQuotedIdentifiersEnabled | Double quotation marks can be used on identifiers. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsPublished | The tables of the database can be published for snapshot or transactional replication, if replication is installed. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsRecursiveTriggersEnabled | Recursive firing of triggers is enabled. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsSubscribed | Database is subscribed to a publication. | 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsSyncWithBackup | The database is either a published database or a distribution database, and can be restored without disrupting transactional replication. | 1 = TRUE 0 = FALSE NULL = Input not valid 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 = Input not valid 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. For more information, see this Microsoft Support Article. | Applies to: Starting with [!INCLUDEssSQL15] SP2. 1 = TRUE 0 = FALSE NULL = Input not valid Base data type: int |
| IsXTPSupported | Indicates whether the database supports In-Memory OLTP, i.e., creating and using 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]), [!INCLUDEssSDSfull]. 1 = TRUE 0 = FALSE NULL = Input not valid, an error, or not applicable Base data type: int |
| LastGoodCheckDbTime | The date and time of the last successful DBCC CHECKDB to run on the specified database. | Applies to: Starting with [!INCLUDEssSQL15] SP2. NULL = Input not valid Base data type: datetime |
| LCID | The Windows locale identifier (LCID) of the collation. | 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 | Recovery model for the database. | 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]. | Can be 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 is using Windows collation >0 = [!INCLUDEssNoVersion] sort order ID NULL = Input not valid or database is 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 is being opened and is 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 is being restored. RECOVERING = Database is recovering and 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 = Data can be read but not modified. READ_WRITE = Data can be read and modified. 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, and 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 is not started. Base data type: int |
sql_variant
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 does not have any permission 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.
The following 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 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 [!INCLUDEssResult]
Collation Edition ServiceObjective MaxSizeInBytes
---------------------------- ------------- ---------------- --------------
SQL_Latin1_General_CP1_CI_AS DataWarehouse DW1000 5368709120 ALTER DATABASE (Transact-SQL)
Database States
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
SERVERPROPERTY (Transact-SQL)