Jaa

DATABASEPROPERTY (Transact-SQL)

  • Artikkeli

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

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use DATABASEPROPERTYEX instead.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DATABASEPROPERTY ( database , property )

Arguments

  • 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 SQL-92 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. 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

int

Exceptions

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

In SQL Server 2005, 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.

Examples

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

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

Here is the result set.

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

See Also

Reference

ALTER DATABASE (Transact-SQL)
DATABASEPROPERTYEX (Transact-SQL)
Metadata Functions (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the "Exceptions" section.