DATABASEPROPERTY
Returns the named database property value for the given database and property name.
Important Use the Microsoft® SQL Server™ 2000 function DATABASEPROPERTYEX to obtain information about the current setting of database options or the properties of a specified database. The DATABASEPROPERTY function is provided for backward compatibility.
Syntax
DATABASEPROPERTY( database , property )
Arguments
database
Is an expression containing the name of the database for which to return the named property information. database is nvarchar(128).
property
Is an expression containing the name of the database property to return. property is varchar(128), and can be one of these values.
| Value | Description | Value returned |
|---|---|---|
| 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 |
| IsAnsiWarningsEnabled | Error or warning messages are issued when standard error conditions occur. | 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 |
| IsBulkCopy | Database allows nonlogged operations. | 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 |
| IsDboOnly | Database is in DBO-only access mode. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsDetached | Database was detached by a detach operation. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsEmergencyMode | Emergency mode is enabled to allow suspect database to be usable. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsFulltextEnabled | Database is full-text enabled. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsInLoad | Database is loading. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsInRecovery | Database is recovering. | 1 = TRUE 0 = FALSE NULL1 = 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 |
| IsNotRecovered | Database failed to recover. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsNullConcat | Null concatenation operand yields NULL. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsOffline | Database is offline. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsQuotedIdentifiersEnabled | Double quotation marks can be used on identifiers. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsReadOnly | Database is in a read-only access mode. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsRecursiveTriggersEnabled | Recursive firing of triggers is enabled. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsShutDown | Database encountered a problem at startup. | 1 = TRUE 0 = FALSE NULL1 = Invalid input |
| IsSingleUser | Database is in single-user access mode. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsSuspect | Database is suspect. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| IsTruncLog | Database truncates its logon checkpoints. | 1 = TRUE 0 = FALSE NULL = Invalid input |
| 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 |
1. Returned value is also NULL if the database has never been started, or has been autoclosed.
Return Types
integer
Examples
This example returns the setting for the IsTruncLog property for the master database.
USE master
SELECT DATABASEPROPERTY('master', 'IsTruncLog')
Here is the result set:
-------------------
1