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.