Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

DATABASEPROPERTY (Transact-SQL)

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

Important noteImportant

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use DATABASEPROPERTYEX instead.

Topic link icon Transact-SQL Syntax Conventions


DATABASEPROPERTY ( database , property ) 
database

Is an expression that contains the name of the database for which to return the named property information. database is nvarchar(128).

property

Is an expression that contains the name of the database property to return. property is varchar(128), and can be one of the following values.

Value

Description

Value returned

IsAnsiNullDefault

Database follows ISO rules for allowing null values.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsAnsiNullsEnabled

All comparisons to a null evaluate to unknown.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsAnsiWarningsEnabled

Error or warning messages are issued when standard error conditions occur.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsAutoClose

Database shuts down cleanly and frees resources after the last user exits.

1 = TRUE

0 = FALSE

NULL = Input not valid

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 = Input not valid

IsAutoShrink

Database files are candidates for automatic periodic shrinking.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsAutoUpdateStatistics

Auto update statistics database option is enabled.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsBulkCopy

Database allows nonlogged operations.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsCloseCursorsOnCommitEnabled

Cursors that are open when a transaction is committed are closed.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsDboOnly

Database is in DBO-only access mode.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsDetached

Database was detached by a detach operation.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsEmergencyMode

Emergency mode is enabled to allow suspect database to be usable.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsFulltextEnabled

Database is full-text enabled.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsInLoad

Database is loading.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsInRecovery

Database is recovering.

1 = TRUE

0 = FALSE NULL1 = Input not valid

IsInStandBy

Database is online as read-only, with restore log allowed.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsLocalCursorsDefault

Cursor declarations default to LOCAL.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsNotRecovered

Database failed to recover.

1 = TRUE

0 = FALSE

NULL = Invalid input

IsNullConcat

Null concatenation operand yields NULL.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsOffline

Database is offline.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsParameterizationForced

PARAMETERIZATION database SET option is FORCED.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsQuotedIdentifiersEnabled

Double quotation marks can be used on identifiers.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsReadOnly

Database is in a read-only access mode.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsRecursiveTriggersEnabled

Recursive firing of triggers is enabled.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsShutDown

Database encountered a problem at startup.

1 = TRUE

0 = FALSE

NULL1 = Input not valid

IsSingleUser

Database is in single-user access mode.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsSuspect

Database is suspect.

1 = TRUE

0 = FALSE

NULL = Input not valid

IsTruncLog

Database truncates its log on checkpoints.

1 = TRUE

0 = FALSE

NULL = Input not valid

Version

Internal version number of the Microsoft SQL Server code with which the database was created. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

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.

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

The following example returns the setting for the IsTruncLog property for themaster database.

USE master;
GO
SELECT DATABASEPROPERTY('master', 'IsTruncLog');


Here is the result set.

-------------------
1

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.