DATABASEPROPERTYEX

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

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. 196609
For example, the default of 196609 is the result of combining the Ignore case, Ignore Kana, and Ignore width options.
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 Database is enabled for full-text and semantic indexing. 0 = FALSE
IsInStandBy Database is online as read-only, with restore log allowed. 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 PARAMETERIZATION database SET option is FORCED. 0 = FALSE
IsQuotedIdentifiersEnabled Double quotation marks can be used on identifiers. 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 Lyftron 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 Maximum database size in bytes. NULL
Recovery Recovery model for the database. 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 The id of the service objective in SQL Database. NULL
SQLSortOrder SQL Server sort order ID supported in earlier versions of SQL Server. 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 Lyftron 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