DATABASEPROPERTYEX (Transact-SQL)
Returns the current setting of the specified database option or property for the specified database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
DATABASEPROPERTYEX ( database , property )
Arguments
database
Is an expression that represents the name of the database for which to return the named property information. database is nvarchar(128).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 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.
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.
Style
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
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
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
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.
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.
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
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 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
LCID
The Windows locale identifier (LCID) of the collation.
LCID value (in decimal format).
Base data type: int
Recovery
Recovery model for the database.
FULL = Full recovery model
BULK_LOGGED = Bulk logged model
SIMPLE = Simple recovery model
Base data type: nvarchar(128)
SQLSortOrder
SQL 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
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 AlwaysOn 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 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
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 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.
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
The following example returns the status of the AUTO_SHRINK database option for the AdventureWorks database.
SELECT DATABASEPROPERTYEX('AdventureWorks2012', '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 the name of the default collation for the AdventureWorks database.
SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'Collation');
Here is the result set.
------------------------------
SQL_Latin1_General_CP1_CI_AI
See Also
Reference
sys.database_files (Transact-SQL)