In SQL Server, you might’ve encountered the OBJECTPROPERTY()
function, only to find out that there’s also a OBJECTPROPERTYEX()
function that appears to do exactly the same thing.
What’s going on here? Why the need for two functions that do the same thing?
My understanding is that Microsoft chose to add OBJECTPROPERTYEX()
to extend the functionality of OBJECTPROPERTY()
, rather than to introduce changes to OBJECTPROPERTY()
that would potentially break existing code on legacy systems.
So there are some differences between the two functions.
What’s Different?
In a nutshell, OBJECTPROPERTYEX()
supports six extra properties, and its return type is different.
Here’s a breakdown.
OBJECTPROPERTY() | OBJECTPROPERTYEX() | |
---|---|---|
Return Type | int | sql_variant |
Number of Supported Properties | 103 | 109 |
Extra Properties |
|
|
Supported Properties |
|
|
Example of the Extra Properties
Here’s an example that shows these extra properties in use.
USE WideWorldImportersDW; DECLARE @objectId int = OBJECT_ID(N'Integration.GenerateDateDimensionColumns'); SELECT OBJECTPROPERTYEX(@objectId, 'BaseType') AS BaseType, OBJECTPROPERTYEX(@objectId, 'IsPrecise') AS IsPrecise, OBJECTPROPERTYEX(@objectId, 'SystemDataAccess') AS SystemDataAccess, OBJECTPROPERTYEX(@objectId, 'TableFullTextSemanticExtraction') AS TableFullTextSemanticExtraction, OBJECTPROPERTYEX(@objectId, 'UserDataAccess') AS UserDataAccess, OBJECTPROPERTYEX(@objectId, 'Cardinality') AS Cardinality;
Result (using vertical output):
BaseType | IF IsPrecise | 0 SystemDataAccess | 1 TableFullTextSemanticExtraction | 0 UserDataAccess | 1 Cardinality | NULL
In this case, the object is a table-valued function, and it returns data for five of the six properties.
Now let’s instead pass in the name of a table to see if we can get a value for cardinality.
USE WideWorldImportersDW; DECLARE @objectId int = OBJECT_ID(N'Dimension.City'); SELECT OBJECTPROPERTYEX(@objectId, 'BaseType') AS BaseType, OBJECTPROPERTYEX(@objectId, 'IsPrecise') AS IsPrecise, OBJECTPROPERTYEX(@objectId, 'SystemDataAccess') AS SystemDataAccess, OBJECTPROPERTYEX(@objectId, 'TableFullTextSemanticExtraction') AS TableFullTextSemanticExtraction, OBJECTPROPERTYEX(@objectId, 'UserDataAccess') AS UserDataAccess, OBJECTPROPERTYEX(@objectId, 'Cardinality') AS Cardinality;
Result (using vertical output):
BaseType | U IsPrecise | NULL SystemDataAccess | NULL TableFullTextSemanticExtraction | 0 UserDataAccess | NULL Cardinality | 116295
This time we get NULL for three of the properties, but we do get a value for the Cardinality property.