Export (0) Print
Expand All
Expand Minimize
3 out of 5 rated this helpful - Rate this topic

DATABASEPROPERTYEX (Transact-SQL)

Updated: 17 July 2006

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

Topic link icon Transact-SQL Syntax Conventions


DATABASEPROPERTYEX ( database , property )
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.

ms186823.note(en-US,SQL.90).gifNote:
If the database is not started, properties that the Microsoft SQL Server 2005 Database Engine 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.

Ignore case 1
Ignore accent 2
Ignore Kana 65536
Ignore width 131072
Style Value

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

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

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

AUTO_UPDATE_STATISTICS database option is enabled.

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

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.

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. For internal use only by SQL Server tools and in upgrade processing.

Version number = Database is open.

NULL = Database is not started.

Base data type: int

sql_variant

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.

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

A. Retrieving the status of the AUTO_SHRINK database option

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

SELECT DATABASEPROPERTYEX('AdventureWorks', '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 AdventureWorks database.

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

Here is the result set.

------------------------------
SQL_Latin1_General_CP1_CI_AI

Release History

17 July 2006

New content:
  • Added the "Exceptions" section.

14 April 2006

Changed content:
  • Corrected and expanded the descriptions of ComparisonStyle and LCID.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.