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');