DATABASEPROPERTYEX

Transact-SQL Reference

Transact-SQL Reference

DATABASEPROPERTYEX

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

Syntax

DATABASEPROPERTYEX( database , property )

Arguments

database

Is an expression that evaluates to the name of the database for which a property setting is to be returned. database is nvarchar(128).

property

Is an expression that indicates the option or property setting to be returned. property is nvarchar(128), and can be one of these values.

Value Description Value returned
Collation Default collation name for the database. Collation name
IsAnsiNullDefault Database follows SQL-92 rules for allowing null values. 1 = TRUE 0 = FALSE
NULL = Invalid input
IsAnsiNullsEnabled All comparisons to a null evaluate to unknown. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiPaddingEnabled Strings are padded to the same length before comparison or insert. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsAnsiWarningsEnabled Error or warning messages are issued when standard error conditions occur. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsArithmeticAbortEnabled Queries are terminated when an overflow or divide-by-zero error occurs during query execution. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoClose Database shuts down cleanly and frees resources after the last user exits. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoCreateStatistics Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoShrink Database files are candidates for automatic periodic shrinking. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsAutoUpdateStatistics Auto update statistics database option is enabled. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsCloseCursorsOnCommitEnabled Cursors that are open when a transaction is committed are closed. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsFulltextEnabled Database is full-text enabled. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsInStandBy Database is online as read-only, with restore log allowed. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsLocalCursorsDefault Cursor declarations default to LOCAL. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsMergePublished The tables of a database can be published for replication, if replication is installed. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsNullConcat Null concatenation operand yields NULL. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsNumericRoundAbortEnabled Errors are generated when loss of precision occurs in expressions. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsQuotedIdentifiersEnabled Double quotation marks can be used on identifiers. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsRecursiveTriggersEnabled Recursive firing of triggers is enabled. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsSubscribed Database can be subscribed for publication. 1 = TRUE
0 = FALSE
NULL = Invalid input
IsTornPageDetectionEnabled Microsoft® SQL Server™ detects incomplete I/O operations caused by power failures or other system outages. 1 = TRUE
0 = FALSE
NULL = Invalid input
Recovery Recovery model for the database. FULL = full recovery model
BULK_LOGGED = bulk logged model
SIMPLE = simple recovery model
SQLSortOrder SQL Server sort order ID supported in previous versions of SQL Server. 0 = Database is using Windows collation
>0 = SQL Server sort order ID
Status Database status. ONLINE = database is available for query
OFFLINE = database was explicitly taken offline
RESTORING = database is being restored
RECOVERING = database is recovering and not yet ready for queries
SUSPECT = database cannot be recovered
Updateability Indicates whether data can be modified. READ_ONLY = data can be read but not modified
READ_WRITE = data can be read and modified
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
Version Internal version number of the Microsoft SQL Server code with which the database was created. For internal use only by SQL Server tools and in upgrade processing. Version number = Database is open
NULL = Database is closed

Return Types

sql_variant

Remarks

This function returns only one property setting at a time.

DATABASEPROPERTY is supported for backward compatibility but does not provide information about the properties added in this release. Also, many properties supported by DATABASEPROPERTY have been replaced by new properties in DATABASEPROPERTYEX.

Examples
A. Retrieving the status of the autoshrink database option

This example returns the status of the autoshrink database option for the Northwind database.

SELECT DATABASEPROPERTYEX('Northwind', 'IsAutoShrink')

Here is the result set (indicates that autoshrink is off):

------------------
0
B. Retrieving the default collation for a database

This example returns the name of the default collation for the Northwind database.

SELECT DATABASEPROPERTYEX('Northwind', 'Collation')

Here is the result set:

------------------------------
SQL_Latin1_General_CP1_CS_AS

See Also

ALTER DATABASE

COLLATE