TechNet
Export (0) Print
Expand All

DATABASEPROPERTYEX (Transact-SQL)

 

Updated: October 27, 2015

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns the current setting of the specified database option or property for the specified database in SQL Server.

Topic link icon 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 SQL Database, 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.

System_CAPS_ICON_note.jpg Note


If the database is not started, properties that the SQL Server 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.

PropertyDescriptionValue returned
CollationDefault collation name for the database.Collation name

NULL = Database is not started.

Base data type: nvarchar(128)
ComparisonStyleThe 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
EditionThe database edition or service tier.Applies to: Azure SQL Database, SQL Data Warehouse.

 

Web = Web Edition Database

Business = Business Edition Database

Basic

Standard

Premium

System (for master database)

NULL = Database is not started.

Base data type: nvarchar(64)
IsAnsiNullDefaultDatabase follows ISO rules for allowing null values.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsAnsiNullsEnabledAll comparisons to a null evaluate to unknown.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsAnsiPaddingEnabledStrings are padded to the same length before comparison or insert.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsAnsiWarningsEnabledError or warning messages are issued when standard error conditions occur.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsArithmeticAbortEnabledQueries 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
IsAutoCloseDatabase shuts down cleanly and frees resources after the last user exits.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsAutoCreateStatisticsQuery optimizer creates single-column statistics, as required, to improve query performance.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsAutoCreateStatisticsIncrementalAuto created single-column statistics are incremental when possible.Applies to: SQL Server 2014 through SQL Server 2016.

 

1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsAutoShrinkDatabase files are candidates for automatic periodic shrinking.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsAutoUpdateStatisticsQuery 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
IsCloseCursorsOnCommitEnabledCursors that are open when a transaction is committed are closed.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsFulltextEnabledDatabase is enabled for full-text and semantic indexing.Applies to: SQL Server 2008 through SQL Server 2016.

 

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 SQL Server. Do not use this column in new development work, and modify applications that currently use any of these columns as soon as possible.
IsInStandByDatabase is online as read-only, with restore log allowed.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsLocalCursorsDefaultCursor declarations default to LOCAL.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsMemoryOptimizedElevateToSnapshotEnabledMemory-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: SQL Server 2014 through SQL Server 2016.

 

1 = TRUE

0 = FALSE

Base data type: int
IsMergePublishedThe 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
IsNullConcatNull concatenation operand yields NULL.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsNumericRoundAbortEnabledErrors are generated when loss of precision occurs in expressions.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsParameterizationForcedPARAMETERIZATION database SET option is FORCED.1 = TRUE

0 = FALSE

NULL = Input not valid
IsQuotedIdentifiersEnabledDouble quotation marks can be used on identifiers.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsPublishedThe 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
IsRecursiveTriggersEnabledRecursive firing of triggers is enabled.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsSubscribedDatabase is subscribed to a publication.1 = TRUE

0 = FALSE

NULL = Input not valid

Base data type: int
IsSyncWithBackupThe 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
IsTornPageDetectionEnabledThe SQL Server Database Engine 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
IsXTPSupportedIndicates whether the database supports In-Memory OLTP, i.e., creating and using 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 2014 through SQL Server 2016), SQL Database.

 Applies to: Azure SQL Database and SQL Server starting SQL Server 2016.

 

1 = TRUE

0 = FALSE

NULL = Input not valid, an error, or not applicable

Base data type: int
LCIDThe Windows locale identifier (LCID) of the collation.LCID value (in decimal format).

Base data type: int
MaxSizeInBytesMaximum database size in bytes.Applies to: Azure SQL Database, SQL Data Warehouse.

 

1073741824

5368709120

10737418240

21474836480

32212254720

42949672960

53687091200

NULL = Database is not started

Base data type: bigint
RecoveryRecovery model for the database.FULL = Full recovery model

BULK_LOGGED = Bulk logged model

SIMPLE = Simple recovery model

Base data type: nvarchar(128)
ServiceObjectiveDescribes the performance level of the database in SQL Database V12 or SQL Data Warehouse.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)
ServiceObjectiveIdThe id of the service objective in SQL Database V12.uniqueidentifier that identifies the service objective.
SQLSortOrderSQL Server sort order ID supported in earlier versions of SQL Server.0 = Database is using Windows collation

>0 = SQL Server sort order ID

NULL = Input not valid or database is not started

Base data type: tinyint
StatusDatabase 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)
UpdateabilityIndicates 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)
UserAccessIndicates 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)
VersionInternal 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 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 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 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.

A. Retrieving the status of the AUTO_SHRINK database option

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. This indicates that AUTO_SHRINK is off.

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

B. Retrieving the default collation for a database

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  

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

Community Additions

ADD
Show:
© 2016 Microsoft