DATABASEPROPERTY

Transact-SQL Reference

Transact-SQL Reference

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

See Also

Control-of-Flow Language

DATABASEPROPERTYEX

DELETE

INSERT

Metadata Functions

SELECT

sp_dboption

UPDATE

WHERE