DATABASEPROPERTYEX (Transact-SQL)

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

Transact-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 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

Return Types

sql_variant

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.

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

See Also

Reference

ALTER DATABASE (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)

Other Resources

Database States
Setting Database Options
Using sql_variant Data

Help and Information

Getting SQL Server 2005 Assistance

Change History