SQL SERVER 2012 – Discontinued Features – DATABASEPROPERTY

Posts Tagged ‘SQL SERVER 2012 – Discontinued Features – DATABASEPROPERTY’

SQL SERVER 2012 – Discontinued Features – DATABASEPROPERTY

Posted in Features, SQL SERVER, tagged DATABASEPROPERTY vs SQL SERVER 2012 - Discontinued Features - DATABASEPROPERTYEX, raresql, SQL, SQL Server, SQL SERVER 2012 - Discontinued Features - DATABASEPROPERTY on March 13, 2013| Leave a Comment »

DATABASEPROPERTY (metadata function) is being discontinued since the emerge of SQL Server 2012 and it is replaced by another metadata function namely DATABASEPROPERTYEX. Both functions take parameters (database name, property name) and return property value on the basis of input parameters. But the major difference between both functions is the return type. DATABASEPROPERTY returns only int datatype while DATABASEPROPERTYEX returns the sql_variant datatype. In simple words, you cannot expect any other data type than int from DATABASEPROPERTY.
I did some research and found some additional difference that I would like to share.

Given below are the scripts, that is written for DATABASEPROPERTYEX & DATABASEPROPERTY to compare both meta data functions.

--Code for DATABASEPROPERTYEX
SELECT DATABASEPROPERTYEX('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTYEX('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoUpdateStatistics')
AS [IsAutoUpdateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTYEX('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTYEX('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTYEX('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTYEX('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTYEX('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTYEX('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTYEX('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTYEX('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTYEX('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTYEX('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTYEX('AdventureWorks2012','Version')
AS [Version]

--Code for DATABASEPROPERTY
SELECT DATABASEPROPERTY('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTY('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTY('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTY('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTY('AdventureWorks2012','IsAutoUpdateStatistics')
AS [[IsAutoUpdateStatistics]],
DATABASEPROPERTY('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTY('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTY('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTY('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTY('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTY('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTY('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTY('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTY('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTY('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTY('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTY('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTY('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTY('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTY('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTY('AdventureWorks2012','Version')
AS [Version]

Given below is the output of both meta data functions and you can see that DATABASEPROPERTY will reveal the correct result whenever the return type of property value is int but on the other hand DATABASEPROPERTYEX will give you the result of any property value because its return type is sql_variant.

Conclusion:
DATABASEPROPERTYEX is one of the handy metadata functions when we need to retrieve the property value on the basis of property and database name.
It also provides an additional info than DATABASEPROPERTY. Remember, DATABASEPROPERTY is discontinued in SQL Server 2012, so don’t use it in further developments. Also if you upgrade SQL Server earlier version to SQL Server 2012, change DATABASEPROPERTY to DATABASEPROPERTYEX in the script.

MSDN : Reference

Read Full Post »