In SQL Server, the OBJECTPROPERTYEX() function returns information about schema-scoped objects in the current database.

This function does exactly the same thing as OBJECTPROPERTY(), except that it supports more properties, and the return value is different. The OBJECTPROPERTYEX() function returns a sql_variant type, whereas OBJECTPROPERTY() returns an int type.

Syntax

The function accepts two arguments: the object’s ID, and the property you want returned.

OBJECTPROPERTYEX ( id , property )

Example 1 – Basic Usage

Here’s an example to demonstrate the basic usage of this function.

SELECT OBJECTPROPERTYEX('885578193', 'BaseType') AS Result;

Result:

+----------+
| Result   |
|----------|
| U        |
+----------+

In this case, there’s an object with an ID of 885578193 and it’s a table. The BaseType of this object is U.

Example 2 – Naming the Object

In the previous example, I knew the ID of the object. In most cases you’ll probably only know the object’s name, but not its ID. In such cases, you can use the OBJECT_ID() function to get the object’s ID, based on its name.

Like this:

SELECT OBJECTPROPERTYEX(OBJECT_ID(N'Artists'), 'BaseType') AS Result;

Result:

+----------+
| Result   |
|----------|
| U        |
+----------+

Example 3 – More Properties

Here’s an example that returns more of the properties that you won’t get returned with OBJECTPROPERTY().

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, 'UserDataAccess') AS UserDataAccess;

Result:

+------------+-------------+--------------------+------------------+
| BaseType   | IsPrecise   | SystemDataAccess   | UserDataAccess   |
|------------+-------------+--------------------+------------------|
| IF         | 0           | 1                  | 1                |
+------------+-------------+--------------------+------------------+

As of writing, the OBJECTPROPERTYEX() function supports 109 properties. See below for a full list of properties supported by this function.

Example 4 – In a WHERE Clause

You can use OBJECTPROPERTYEX() in a WHERE clause if required.

In this example, I run two queries: one that returns tables that have a foreign key, and one that returns tables that are referenced by a foreign key.

USE Music;
SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTYEX(object_id, 'TableHasForeignKey') = 1;

SELECT 
  SCHEMA_NAME(schema_id) AS [Schema], 
  name AS [Table]
FROM sys.tables
WHERE OBJECTPROPERTYEX(object_id, 'TableHasForeignRef') = 1;

Result:

Changed database context to 'Music'.
+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Artists |
| dbo      | Albums  |
+----------+---------+
(2 rows affected)
+----------+---------+
| Schema   | Table   |
|----------+---------|
| dbo      | Artists |
| dbo      | Genres  |
| dbo      | Country |
+----------+---------+
(3 rows affected)

Full List of Properties

Here’s a full list of properties supported by the OBJECTPROPERTYEX() function:

• 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

See the Microsoft documentation for a detailed explanation of each property.

Also see OBJECTPROPERTY() vs OBJECTPROPERTYEX() for a more detailed explanation of the differences between these two functions.