DATABASEPROPERTYEX() in SQL Server

DATABASEPROPERTYEX()
It is the function used to return the different information about the current setting of the specified database option or property.

Syntax :

DATABASEPROPERTYEX ( 'databasename', 'propertyname' )  

Now, here you will see the property description as following.

• databasename :
  It is the name of the database for which DATABASEPROPERTYEX will result in the property name information, databasename has an nvarchar(128) data type.
• propertyname :
  It is an expression that defines the name of the database property to have resulted, propertyname has varchar(128) data type.

The following are a few property names that DATABASEPROPERTYEX() function provides info.

1. Collation :
   This property defines the default collation name for the database.
2. ComparisonStyle :
   This property defines the Windows comparison variety of the collation.
3. Edition :
   This property defines the database edition or service tier.
4. IsAnsiNullDefault :
   This property defines if the database follows ISO rules for permitting null values.
5. IsAnsiNullsEnabled :
   This property defines all comparisons to a null assessment to the unknown.
6. IsAnsiPaddingEnabled :
   This property defines strings are padded to the identical length before comparison or insert.
7. IsAnsiWarningsEnabled :
   This property defines SQL Server issues an error or warning messages once commonplace error conditions occur.
8. IsArithmeticAbortEnabled :
   This property defines queries to finish once an overflow or divide-by-zero error happens throughout query execution.
9. IsAutoClose :
   This property defines the database shuts down cleanly and frees resources when the last user exits.
10. IsAutoCreateStatistics :
    This property defines query optimizer creates single-column statistics, as needed, to boost query performance.
11. IsAutoCreateStatisticsIncremental :
    This property defines auto-created single-column statistics are incremental once possible.
12. IsAutoShrink :
    This property defines database files are candidates for automatic periodic shrinking.
13. IsAutoUpdateStatistics :
    This property defines when a query uses potentially out-of-date existing statistics, the query optimizer updates those statistics.
14. IsClone :
    This property defines a database might be a schema- and statistics only copy of a user database created with DBCC CLONE DATABASE.
15. IsCloseCursorsOnCommitEnabled :
    This property defines when a transaction commits, all open cursors will close.
16. IsFulltextEnabled :
    This property defines the database is enabled for full-text and linguistics indexing.
17. IsInStandBy :
    This property defines database is online as read-only, with restore log allowed.
18. IsLocalCursorsDefault :
    This property defines pointer declarations default to native.
19. IsNullConcat :
    This property defines null concatenation operand yields NULL.
20. IsNumericRoundAbortEnabled :
    This property defines errors are generated once a loss of precision happens in expressions.
21. IsQuotedIdentifiersEnabled :
    This property defines double quotation marks on identifiers are allowed.
22. IsPublished :
    This property defines if replication is there, SQL Server supports database table publication for replication.
23. IsRecursiveTriggersEnabled :
    This property defines the recursive firing of triggers is enabled.
24. IsSubscribed :
    This property defines the database is subscribed to a publication.
25. IsSyncWithBackup :
    This property defines the database is either a broadcast database or a distributed database, and it supports a restoration that is able to not disrupt transnational replication.
26. IsTornPageDetectionEnabled :
    This property defines the SQL Server Database Engine detects incomplete I/O operations caused by power failures or different system outages.
27. IsXTPSupported :
    This property indicates whether the database supports In-Memory OLTP.

Examples –
Here is the screenshot.