DATABASEPROPERTY (T-SQL)

Returns the named database property value for the given database and property name.

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
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 going through the loading process.	1 = True 0 = False NULL = Invalid input
IsInRecovery	Database is recovering.	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
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 NULL = 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 log on 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.

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:

-------------------

1

See Also