Question from SQLAuthority.com: What is the difference between DATABASEPROPERTY and DATABASEPROPERTYEX in SQL Server?
Answer from Rakesh Kumar: DATABASEPROPERTY is supported for backward compatibility but does not provide information about the properties added in this release. Also, many properties supported by DATABASEPROPERTY have been replaced by new properties in DATABASEPROPERTYEX.- source (MSDN).
Answer from Alphonso Jones: The only real difference I can see is one, the number of properties contained and the other is that EX returns a sql_variant while DATABASEPROPERTY returns only int.
Answer from Ambati Venkatasiva: Both are system meta data functions. DATABASEPROPERTYEX Returns the current setting of the specified database option. DATABASEPROPERTYEX returns the sq-varient value and DATABASEPROPERTY returns integer value.
Answer from Rama Sankar Molleti:
Here is the best example about databasepropertyex
SELECT DATABASEPROPERTYEX('dbname', 'Collation')
Whereas with databaseproperty it retuns nothing as the return type for this
is integer. Sql_variant datatype stores values of various sql server
supported datatypes except text, ntext, image and timestamp.
Answer from Alok Seth:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
--Result - ONLINE
SELECT DATABASEPROPERTY('AdventureWorks', 'Status')
--Result - NULL
Use DATABASEPROPERTYEX as it is the only function supported in future version as well it returns status of various database properties which does not exists with DATABASEPROPERTY.
Reference: Pinal Dave (https://blog.sqlauthority.com)