Share via

  • Article

DATABASEPROPERTYEX (Transact-SQL)

Returns the current setting of the specified database option or property for the specified database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DATABASEPROPERTYEX ( database , property )

Arguments

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

  • property
    Is an expression that represents the name of the database property to return. property is varchar(128), and can be one of the following values. The return type is sql_variant. The following table shows the base data type for each property value.

    Note

    If the database is not started, properties that the SQL Server retrieves by accessing the database directly instead of retrieving the value from metadata will return NULL. That is, if the database has AUTO_CLOSE set to ON, or the database is otherwise offline.

    Property

    Description

    Value returned

    Collation

    Default collation name for the database.

    Collation name

    NULL = Database is not started.

    Base data type: nvarchar(128)

    ComparisonStyle

    The Windows comparison style of the collation. ComparisonStyle is a bitmap that is calculated by using the following values.

    StyleValue
    Ignore case1
    Ignore accent2
    Ignore Kana65536
    Ignore width131072

    For example, the default of 196609 is the result of combining the Ignore case, Ignore Kana, and Ignore width options.

    Returns the comparison style.

    Returns 0 for all binary collations.

    Base data type: int

    IsAnsiNullDefault

    Database follows ISO rules for allowing null values.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsAnsiNullsEnabled

    All comparisons to a null evaluate to unknown.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsAnsiPaddingEnabled

    Strings are padded to the same length before comparison or insert.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsAnsiWarningsEnabled

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

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsArithmeticAbortEnabled

    Queries are ended when an overflow or divide-by-zero error occurs during query execution.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsAutoClose

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

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsAutoCreateStatistics

    Query optimizer creates single-column statistics, as required, to improve query performance.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsAutoShrink

    Database files are candidates for automatic periodic shrinking.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsAutoUpdateStatistics

    Query optimizer updates existing statistics when they are used by a query and might be out-of-date.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsCloseCursorsOnCommitEnabled

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

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsFulltextEnabled

    Database is full-text enabled.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    NoteNote
    The value of this property has no effect. User databases are always enabled for full-text search. This column will be removed in a future release of SQL Server. Do not use this column in new development work, and modify applications that currently use any of these columns as soon as possible.

    IsInStandBy

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

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsLocalCursorsDefault

    Cursor declarations default to LOCAL.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsMergePublished

    The tables of a database can be published for merge replication, if replication is installed.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsNullConcat

    Null concatenation operand yields NULL.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsNumericRoundAbortEnabled

    Errors are generated when loss of precision occurs in expressions.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    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

    Base data type: int

    IsPublished

    The tables of the database can be published for snapshot or transactional replication, if replication is installed.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsRecursiveTriggersEnabled

    Recursive firing of triggers is enabled.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsSubscribed

    Database is subscribed to a publication.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsSyncWithBackup

    The database is either a published database or a distribution database, and can be restored without disrupting transactional replication.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    IsTornPageDetectionEnabled

    The SQL Server Database Engine detects incomplete I/O operations caused by power failures or other system outages.

    1 = TRUE

    0 = FALSE

    NULL = Input not valid

    Base data type: int

    LCID

    The Windows locale identifier (LCID) of the collation.

    LCID value (in decimal format).

    Base data type: int

    For a list of LCID values (in hexadecimal format), see Collation Settings in Setup.

    Recovery

    Recovery model for the database.

    FULL = Full recovery model

    BULK_LOGGED = Bulk logged model

    SIMPLE = Simple recovery model

    Base data type: nvarchar(128)

    SQLSortOrder

    SQL Server sort order ID supported in earlier versions of SQL Server.

    0 = Database is using Windows collation

    >0 = SQL Server sort order ID

    NULL = Input not valid or database is not started

    Base data type: tinyint

    Status

    Database status.

    ONLINE = Database is available for query.

    NoteNote
    The ONLINE status may be returned while the database is being opened and is not yet recovered. To identify when a database can accept connections, query the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value.

    OFFLINE = Database was explicitly taken offline.

    RESTORING = Database is being restored.

    RECOVERING = Database is recovering and not yet ready for queries.

    SUSPECT = Database did not recover.

    EMERGENCY = Database is in an emergency, read-only state. Access is restricted to sysadmin members

    Base data type: nvarchar(128)

    Updateability

    Indicates whether data can be modified.

    READ_ONLY = Data can be read but not modified.

    READ_WRITE = Data can be read and modified.

    Base data type: nvarchar(128)

    UserAccess

    Indicates which users can access the database.

    SINGLE_USER = Only one db_owner, dbcreator, or sysadmin user at a time

    RESTRICTED_USER = Only members of db_owner, dbcreator, and sysadmin roles

    MULTI_USER = All users

    Base data type: nvarchar(128)

    Version

    Internal version number of the 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 not started.

    Base data type: int

Return Types

sql_variant

Exceptions

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.

Remarks

DATABASEPROPERTYEX returns only one property setting at a time. To display multiple property settings, use the sys.databases catalog view.

Examples

A. Retrieving the status of the AUTO_SHRINK database option

The following example returns the status of the AUTO_SHRINK database option for the AdventureWorks2008R2 database.

SELECT DATABASEPROPERTYEX('AdventureWorks2008R2', 'IsAutoShrink');

Here is the result set. This indicates that AUTO_SHRINK is off.

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

0

B. Retrieving the default collation for a database

The following example returns the name of the default collation for the AdventureWorks2008R2 database.

SELECT DATABASEPROPERTYEX('AdventureWorks2008R2', 'Collation');

Here is the result set.

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

SQL_Latin1_General_CP1_CI_AI