DATABASEPROPERTY (Transact-SQL)

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

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