DATABASEPROPERTYEX

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

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

For SQL Database, must be the name of the current database. Returns NULL for all properties if a different database name is provided.

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.

Property

Description

Value returned

Collation

Default collation name for the database.

“SQL_Latin1_General_CP1_CI_AS” Base data type: nvarchar(128)

CollationID

Default collation ID for the database.

872468488

ComparisonStyle

The Windows comparison style of the collation. ComparisonStyle is a bitmap that is calculated by using the following values for the possible styles. For example, the default of 196609 is the result of combining the Ignore case, Ignore Kana, and Ignore width options.

196609

Edition

The database edition or service tier.

“Enterprise Edition” Base data type: nvarchar(64)

IsAnsiNullDefault

Database follows ISO rules for allowing null values.

0 = FALSE Base data type: int

IsAnsiNullsEnabled

All comparisons to a null evaluate to unknown.

0 = FALSE Base data type: int

IsAnsiPaddingEnabled

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

0 = FALSE Base data type: int

IsAnsiWarningsEnabled

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

0 = FALSE Base data type: int

IsArithmeticAbortEnabled

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

0 = FALSE Base data type: int

IsAutoClose

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

0 = FALSE Base data type: int

IsAutoCreateStatistics

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

1 = TRUE Base data type: int

IsAutoCreateStatisticsIncremental

Auto created single-column statistics are incremental when possible.

NULL = Input not valid Base data type: int

IsAutoShrink

Database files are candidates for automatic periodic shrinking.

0 = FALSE 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 Base data type: int

IsCloseCursorsOnCommitEnabled

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

1 = TRUE Base data type: int

IsFulltextEnabled IsInStandBy

Database is enabled for full-text and semantic indexing. Database is online as read-only, with restore log allowed.

0 = FALSE 0 = FALSE Base data type: int

IsLocalCursorsDefault

Cursor declarations default to LOCAL.

0 = FALSE Base data type: int

IsMergePublished

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

0 = FALSE Base data type: int

IsNullConcat

Null concatenation operand yields NULL.

0 = FALSE Base data type: int

IsNumericRoundAbortEnabled

Errors are generated when loss of precision occurs in expressions.

0 = FALSE Base data type: int

IsParameterizationForced IsQuotedIdentifiersEnabled

PARAMETERIZATION database SET option is FORCED. Double quotation marks can be used on identifiers.

0 = FALSE 0 = FALSE Base data type: int

IsPublished

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

0 = FALSE Base data type: int

IsRecursiveTriggersEnabled

Recursive firing of triggers is enabled.

0 = FALSE Base data type: int

IsSubscribed

Database is subscribed to a publication.

0 = FALSE Base data type: int

IsSyncWithBackup

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

0 = FALSE Base data type: int

IsTornPageDetectionEnabled

Querona Engine detects incomplete I/O operations caused by power failures or other system outages.

0 = FALSE Base data type: int

LCID

The Windows locale identifier (LCID) of the collation.

1045 (in decimal format). Base data type: int

MaxSizeInBytes Recovery

Maximum database size in bytes. Recovery model for the database.

NULL SIMPLE = Simple recovery model Base data type: nvarchar(128)

ServiceObjective

Describes the performance level of the database in SQL Database.

NULL: database not started Base data type: nvarchar(32)

ServiceObjectiveId SQLSortOrder

The id of the service objective in SQL Database. SQL Server sort order ID supported in earlier versions of SQL Server.

NULL 0 = Database is using Windows collation Base data type: tinyint

Status

Database status.

ONLINE = Database is available for query. Base data type: nvarchar(128)

Updateability

Indicates whether data can be modified.

READ_WRITE = Data can be read and modified. Base data type: nvarchar(128)

UserAccess

Indicates which users can access the database.

MULTI_USER = All users Base data type: nvarchar(128)

Version

Internal version number of the Querona code with which the database was created. Identified for informational purposes only.

NULL Base data type: int

Return types

sql_variant

Example

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

See Also