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		BaseType IsPrecise SystemDataAccess TableFullTextSemanticExtraction UserDataAccess Cardinality
Supported Properties	CnstIsClustKey CnstIsColumn CnstIsDeleteCascade CnstIsDisabled CnstIsNonclustKey CnstIsNotRepl CnstIsNotTrusted CnstIsUpdateCascade ExecIsAfterTrigger ExecIsAnsiNullsOn ExecIsDeleteTrigger ExecIsFirstDeleteTrigger ExecIsFirstInsertTrigger ExecIsFirstUpdateTrigger ExecIsInsertTrigger ExecIsInsteadOfTrigger ExecIsLastDeleteTrigger ExecIsLastInsertTrigger ExecIsLastUpdateTrigger ExecIsQuotedIdentOn ExecIsStartup ExecIsTriggerDisabled ExecIsTriggerNotForRepl ExecIsUpdateTrigger ExecIsWithNativeCompilation HasAfterTrigger HasDeleteTrigger HasInsertTrigger HasInsteadOfTrigger HasUpdateTrigger IsAnsiNullsOn IsCheckCnst IsConstraint IsDefault IsDefaultCnst IsDeterministic IsEncrypted IsExecuted IsExtendedProc IsForeignKey IsIndexed IsIndexable IsInlineFunction IsMSShipped IsPrimaryKey IsProcedure IsQuotedIdentOn IsQueue IsReplProc IsRule IsScalarFunction IsSchemaBound IsSystemTable IsSystemVerified IsTable IsTableFunction IsTrigger IsUniqueCnst IsUserTable IsView OwnerId SchemaId TableDeleteTrigger TableDeleteTriggerCount TableFullTextMergeStatus TableFullTextBackgroundUpdateIndexOn TableFulltextCatalogId TableFulltextChangeTrackingOn TableFulltextDocsProcessed TableFulltextFailCount TableFulltextItemCount TableFulltextKeyColumn TableFulltextPendingChanges TableFulltextPopulateStatus TableHasActiveFulltextIndex TableHasCheckCnst TableHasClustIndex TableHasDefaultCnst TableHasDeleteTrigger TableHasForeignKey TableHasForeignRef TableHasIdentity TableHasIndex TableHasInsertTrigger TableHasNonclustIndex TableHasPrimaryKey TableHasRowGuidCol TableHasTextImage TableHasTimestamp TableHasUniqueCnst TableHasUpdateTrigger TableHasVarDecimalStorageFormat TableInsertTrigger TableInsertTriggerCount TableIsFake TableIsLockedOnBulkLoad TableIsMemoryOptimized TableIsPinned TableTextInRowLimit TableUpdateTrigger TableUpdateTriggerCount TableHasColumnSet TableTemporalType	BaseType CnstIsClustKey CnstIsColumn CnstIsDeleteCascade CnstIsDisabled CnstIsNonclustKey CnstIsNotRepl CnstIsNotTrusted CnstIsUpdateCascade ExecIsAfterTrigger ExecIsAnsiNullsOn ExecIsDeleteTrigger ExecIsFirstDeleteTrigger ExecIsFirstInsertTrigger ExecIsFirstUpdateTrigger ExecIsInsertTrigger ExecIsInsteadOfTrigger ExecIsLastDeleteTrigger ExecIsLastInsertTrigger ExecIsLastUpdateTrigger ExecIsQuotedIdentOn ExecIsStartup ExecIsTriggerDisabled ExecIsTriggerNotForRepl ExecIsUpdateTrigger ExecIsWithNativeCompilation HasAfterTrigger HasDeleteTrigger HasInsertTrigger HasInsteadOfTrigger HasUpdateTrigger IsAnsiNullsOn IsCheckCnst IsConstraint IsDefault IsDefaultCnst IsDeterministic IsEncrypted IsExecuted IsExtendedProc IsForeignKey IsIndexed IsIndexable IsInlineFunction IsMSShipped IsPrecise IsPrimaryKey IsProcedure IsQuotedIdentOn IsQueue IsReplProc IsRule IsScalarFunction IsSchemaBound IsSystemTable IsSystemVerified IsTable IsTableFunction IsTrigger IsUniqueCnst IsUserTable IsView OwnerId SchemaId SystemDataAccess TableDeleteTrigger TableDeleteTriggerCount TableFullTextMergeStatus TableFullTextBackgroundUpdateIndexOn TableFulltextCatalogId TableFullTextChangeTrackingOn TableFulltextDocsProcessed TableFulltextFailCount TableFulltextItemCount TableFulltextKeyColumn TableFulltextPendingChanges TableFulltextPopulateStatus TableFullTextSemanticExtraction TableHasActiveFulltextIndex TableHasCheckCnst TableHasClustIndex TableHasDefaultCnst TableHasDeleteTrigger TableHasForeignKey TableHasForeignRef TableHasIdentity TableHasIndex TableHasInsertTrigger TableHasNonclustIndex TableHasPrimaryKey TableHasRowGuidCol TableHasTextImage TableHasTimestamp TableHasUniqueCnst TableHasUpdateTrigger TableHasVarDecimalStorageFormat TableInsertTrigger TableInsertTriggerCount TableIsFake TableIsLockedOnBulkLoad TableIsMemoryOptimized TableIsPinned TableTextInRowLimit TableUpdateTrigger TableUpdateTriggerCount UserDataAccess TableHasColumnSet Cardinality TableTemporalType

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.