SALES: 1-800-867-1380

DATABASEPROPERTYEX (Azure SQL Database)

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see DATABASEPROPERTYEX (Transact-SQL).

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


DATABASEPROPERTYEX ( 'database' , 'property' )

database
Is an expression that represents the name of the database for which to return the named property information. The database is nvarchar(128). The database argument should be the same as the current database; otherwise all DATABASEPROPERTYEX return NULL.

property
Is an expression that represents the name of the database property to return. The 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.

Base data type: nvarchar(128)

SQL_Latin1_General_CP1_CI_AS

ComparisonStyle

The Windows comparison style of the collation. ComparisonStyle is a bitmap that is calculated by using the following values:

  • Ignore case: 1

  • Ignore accent: 2

  • Ignore Kana: 65536

  • Ignore width: 131072

For example, the default of 196609 is the result of combining the Ignore case, Ignore Kana, and Ignore width options.

Base data type: int

196609

Edition

The database edition for Microsoft Azure SQL Database.

Base data type: nvarchar(64)

”Web” = Web Edition Database

”Business” = Business Edition Database

NULL = Database is not started.

IsAnsiNullDefault

Database follows ISO rules for allowing null values.

Base data type: int

0 = FALSE

IsAnsiNullsEnabled

All comparisons to a null evaluate to unknown.

Base data type: int

0 = FALSE

IsAnsiPaddingEnabled

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

Base data type: int

0 = FALSE

IsAnsiWarningsEnabled

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

Base data type: int

0 = FALSE

IsArithmeticAbortEnabled

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

Base data type: int

0 = FALSE

IsAutoClose

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

Base data type: int

0 = FALSE

IsAutoCreateStatistics

Missing statistics that are required by a query for optimization are automatically built during query optimization.

Base data type: int

1 = TRUE

IsAutoShrink

Database files are candidates for automatic periodic shrinking.

Base data type: int

0 = FALSE

IsAutoUpdateStatistics

Existing statistics are automatically updated when the statistics become out of date because the data in the tables has changed.

Base data type: int

1 = TRUE

IsCloseCursorsOnCommitEnabled

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

Base data type: int

0 = FALSE

IsInStandBy

Database is online as read only, with restore log allowed.

Base data type: int

0 = FALSE

IsLocalCursorsDefault

Cursor declarations default to LOCAL.

Base data type: int

0 = FALSE

IsMergePublished

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

Base data type: int

0 = FALSE

IsNullConcat

Null concatenation operand yields NULL.

Base data type: int

0 = FALSE

IsNumericRoundAbortEnabled

Errors are generated when loss of precision occurs in expressions.

Base data type: int

0 = FALSE

IsParameterizationForced

PARAMETERIZATION database SET option is FORCED.

Base data type: int

0 = FALSE

IsQuotedIdentifiersEnabled

Double quotation marks can be used on identifiers.

Base data type: int

0 = FALSE

IsPublished

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

Base data type: int

0 = FALSE

IsRecursiveTriggersEnabled

Recursive firing of triggers is enabled.

Base data type: int

0 = FALSE

IsSubscribed

Database is subscribed to a publication.

Base data type: int

0 = FALSE

IsSyncWithBackup

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

Base data type: int

0 = FALSE

IsTornPageDetectionEnabled

The SQL Server Database Engine detects incomplete I/O operations caused by power failures or other system outages.

Base data type: int

0 = FALSE

LCID

The Windows locale identifier (LCID) of the collation.

Base data type: int

1033

MaxSizeInBytes

Maximum database size in bytes for Microsoft Azure SQL Database.

Base data type: bigint

1073741824

5368709120

10737418240

21474836480

32212254720

42949672960

53687091200

NULL = Database is not started.

Recovery

Recovery model for the database.

Base data type: nvarchar(128)

FULL = Full recovery model

SQLSortOrder

SQL Server sort order ID supported in earlier versions of SQL Server.

Base data type: tinyint

52

Status

Database status.

Base data type: nvarchar(128)

ONLINE = Database is available for query.

Updateability

Indicates whether data can be modified.

Base data type: nvarchar(128)

READ_ONLY = Data can be read but not modified.

READ_WRITE = Data can be read and modified.

UserAccess

Indicates which users can access the database.

Base data type: nvarchar(128)

MULTI_USER = All users

Version

Internal version number of the SQL Server code with which the database was created. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Base data type: int

Version number = Database is open.

NULL = Database is not started.

For more information about the arguments and the DATABASEPROPERTYEX statement, see DATABASEPROPERTYEX (Transact-SQL) in SQL Server Books Online.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft