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