Returns the current setting of the specified database option or property for the specified database.
DATABASEPROPERTYEX ( database , property )
Is an expression that represents the name of the database for which to return the named property information. database is nvarchar(128).
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.
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.
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
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoShrink');
Here is the result set. This indicates that AUTO_SHRINK is off.
B. Retrieving the default collation for a database
The following example returns the name of the default collation for the
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation');
Here is the result set.
Help and Information
17 July 2006
14 April 2006