| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
OBJECTPROPERTYEX (Transact-SQL) | OBJECTPROPERTYEX (Transact-SQL) | VanMSFT | vanto | 03/15/2017 | sql | t-sql | reference |
|
|
| >= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current ||=fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
Returns information about schema-scoped objects in the current database. For a list of these objects, see sys.objects (Transact-SQL). OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
OBJECTPROPERTYEX ( id , property ) id
Is an expression that represents the ID of the object in the current database. id is int and is assumed to be a schema-scoped object in the current database context.
property
Is an expression that contains the information to be returned for the object specified by id. The return type is sql_variant. The following table shows the base data type for each property value.
Note
Unless noted otherwise, NULL is returned when property is not a valid property name, id is not a valid object ID, id is an unsupported object type for the specified property, or the caller does not have permission to view the object's metadata.
| Property name | Object type | Description and values returned |
|---|---|---|
| BaseType | Any schema-scoped object | Identifies the base type of the object. When the specified object is a SYNONYM, the base type of the underlying object is returned. Nonnull = Object type Base data type: char(2) |
| CnstIsClustKey | Constraint | PRIMARY KEY constraint with a clustered index. 1 = True 0 = False Base data type: int |
| CnstIsColumn | Constraint | CHECK, DEFAULT, or FOREIGN KEY constraint on a single column. 1 = True 0 = False Base data type: int |
| CnstIsDeleteCascade | Constraint | FOREIGN KEY constraint with the ON DELETE CASCADE option. 1 = True 0 = False Base data type: int |
| CnstIsDisabled | Constraint | Disabled constraint. 1 = True 0 = False Base data type: int |
| CnstIsNonclustKey | Constraint | PRIMARY KEY constraint with a nonclustered index. 1 = True 0 = False Base data type: int |
| CnstIsNotRepl | Constraint | Constraint is defined by using the NOT FOR REPLICATION keywords. 1 = True 0 = False Base data type: int |
| CnstIsNotTrusted | Constraint | Constraint was enabled without checking existing rows. Therefore, the constraint may not hold for all rows. 1 = True 0 = False Base data type: int |
| CnstIsUpdateCascade | Constraint | FOREIGN KEY constraint with the ON UPDATE CASCADE option. 1 = True 0 = False Base data type: int |
| ExecIsAfterTrigger | Trigger | AFTER trigger. 1 = True 0 = False Base data type: int |
| ExecIsAnsiNullsOn | [!INCLUDEtsql] function, [!INCLUDEtsql] procedure, [!INCLUDEtsql] trigger, view | The setting of ANSI_NULLS at creation time. 1 = True 0 = False Base data type: int |
| ExecIsDeleteTrigger | Trigger | DELETE trigger. 1 = True 0 = False Base data type: int |
| ExecIsFirstDeleteTrigger | Trigger | The first trigger fired when a DELETE is executed against the table. 1 = True 0 = False Base data type: int |
| ExecIsFirstInsertTrigger | Trigger | The first trigger fired when an INSERT is executed against the table. 1 = True 0 = False Base data type: int |
| ExecIsFirstUpdateTrigger | Trigger | The first trigger fired when an UPDATE is executed against the table. 1 = True 0 = False Base data type: int |
| ExecIsInsertTrigger | Trigger | INSERT trigger. 1 = True 0 = False Base data type: int |
| ExecIsInsteadOfTrigger | Trigger | INSTEAD OF trigger. 1 = True 0 = False Base data type: int |
| ExecIsLastDeleteTrigger | Trigger | Last trigger fired when a DELETE is executed against the table. 1 = True 0 = False Base data type: int |
| ExecIsLastInsertTrigger | Trigger | Last trigger fired when an INSERT is executed against the table. 1 = True 0 = False Base data type: int |
| ExecIsLastUpdateTrigger | Trigger | Last trigger fired when an UPDATE is executed against the table. 1 = True 0 = False Base data type: int |
| ExecIsQuotedIdentOn | [!INCLUDEtsql] function, [!INCLUDEtsql] procedure, [!INCLUDEtsql] trigger, view | Setting of QUOTED_IDENTIFIER at creation time. 1 = True 0 = False Base data type: int |
| ExecIsStartup | Procedure | Startup procedure. 1 = True 0 = False Base data type: int |
| ExecIsTriggerDisabled | Trigger | Disabled trigger. 1 = True 0 = False Base data type: int |
| ExecIsTriggerNotForRepl | Trigger | Trigger defined as NOT FOR REPLICATION. 1 = True 0 = False Base data type: int |
| ExecIsUpdateTrigger | Trigger | UPDATE trigger. 1 = True 0 = False Base data type: int |
| ExecIsWithNativeCompilation | [!INCLUDEtsql] Procedure | Applies to: [!INCLUDEssSQL14] and later. Procedure is natively compiled. 1 = True 0 = False Base data type: int |
| HasAfterTrigger | Table, view | Table or view has an AFTER trigger. 1 = True 0 = False Base data type: int |
| HasDeleteTrigger | Table, view | Table or view has a DELETE trigger. 1 = True 0 = False Base data type: int |
| HasInsertTrigger | Table, view | Table or view has an INSERT trigger. 1 = True 0 = False Base data type: int |
| HasInsteadOfTrigger | Table, view | Table or view has an INSTEAD OF trigger. 1 = True 0 = False Base data type: int |
| HasUpdateTrigger | Table, view | Table or view has an UPDATE trigger. 1 = True 0 = False Base data type: int |
| IsAnsiNullsOn | [!INCLUDEtsql] function, [!INCLUDEtsql] procedure, table, [!INCLUDEtsql] trigger, view | Specifies that the ANSI NULLS option setting for the table is ON, meaning all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists. 1 = True 0 = False Base data type: int |
| IsCheckCnst | Any schema-scoped object | CHECK constraint. 1 = True 0 = False Base data type: int |
| IsConstraint | Any schema-scoped object | Constraint. 1 = True 0 = False Base data type: int |
| IsDefault | Any schema-scoped object | Applies to: [!INCLUDEsql2008-md] and later. Bound default. 1 = True 0 = False Base data type: int |
| IsDefaultCnst | Any schema-scoped object | DEFAULT constraint. 1 = True 0 = False Base data type: int |
| IsDeterministic | Scalar and table-valued functions, view | The determinism property of the function or view. 1 = Deterministic 0 = Not Deterministic Base data type: int |
| IsEncrypted | [!INCLUDEtsql] function, [!INCLUDEtsql] procedure, table, [!INCLUDEtsql] trigger, view | Indicates that the original text of the module statement was converted to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in [!INCLUDEssVersion2005]. Users without access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at run time. 1 = Encrypted 0 = Not encrypted Base data type: int |
| IsExecuted | Any schema-scoped object | Specifies the object can be executed (view, procedure, function, or trigger). 1 = True 0 = False Base data type: int |
| IsExtendedProc | Any schema-scoped object | Extended procedure. 1 = True 0 = False Base data type: int |
| IsForeignKey | Any schema-scoped object | FOREIGN KEY constraint. 1 = True 0 = False Base data type: int |
| IsIndexed | Table, view | A table or view with an index. 1 = True 0 = False Base data type: int |
| IsIndexable | Table, view | A table or view on which an index may be created. 1 = True 0 = False Base data type: int |
| IsInlineFunction | Function | Inline function. 1 = Inline function 0 = Not inline function Base data type: int |
| IsMSShipped | Any schema-scoped object | An object created during installation of [!INCLUDEssNoVersion]. 1 = True 0 = False Base data type: int |
| IsPrecise | Computed column, function, user-defined type, view | Indicates whether the object contains an imprecise computation, such as floating point operations. 1 = Precise 0 = Imprecise Base data type: int |
| IsPrimaryKey | Any schema-scoped object | PRIMARY KEY constraint. 1 = True 0 = False Base data type: int |
| IsProcedure | Any schema-scoped object | Procedure. 1 = True 0 = False Base data type: int |
| IsQuotedIdentOn | CHECK constraint, DEFAULT definition, [!INCLUDEtsql] function, [!INCLUDEtsql] procedure, table, [!INCLUDEtsql] trigger, view | Specifies that the quoted identifier setting for the object is ON, meaning double quotation marks delimit identifiers in all expressions involved in the object definition. 1 = True 0 = False Base data type: int |
| IsQueue | Any schema-scoped object | Service Broker Queue 1 = True 0 = False Base data type: int |
| IsReplProc | Any schema-scoped object | Replication procedure. 1 = True 0 = False Base data type: int |
| IsRule | Any schema-scoped object | Bound rule. 1 = True 0 = False Base data type: int |
| IsScalarFunction | Function | Scalar-valued function. 1 = Scalar-valued function 0 = Not scalar-valued function Base data type: int |
| IsSchemaBound | Function, Procedure, view | A schema bound function or view created by using SCHEMABINDING. 1 = Schema-bound 0 = Not schema-bound Base data type: int |
| IsSystemTable | Table | System table. 1 = True 0 = False Base data type: int |
| IsSystemVerified | Computed column, function, user-defined type, view | The precision and determinism properties of the object can be verified by [!INCLUDEssNoVersion]. 1 = True 0 = False Base data type: int |
| IsTable | Table | Table. 1 = True 0 = False Base data type: int |
| IsTableFunction | Function | Table-valued function. 1 = Table-valued function 0 = Not table-valued function Base data type: int |
| IsTrigger | Any schema-scoped object | Trigger. 1 = True 0 = False Base data type: int |
| IsUniqueCnst | Any schema-scoped object | UNIQUE constraint. 1 = True 0 = False Base data type: int |
| IsUserTable | Table | User-defined table. 1 = True 0 = False Base data type: int |
| IsView | View | View. 1 = True 0 = False Base data type: int |
| OwnerId | Any schema-scoped object | Owner of the object. Note: The schema owner is not necessarily the object owner. For example, child objects (those where parent_object_id is nonnull) will always return the same owner ID as the parent. Nonnull = Database user ID of the object owner. NULL = Unsupported object type, or object ID is not valid. Base data type: int |
| SchemaId | Any schema-scoped object | The ID of the schema associated with the object. Nonnull = Schema ID of the object. Base data type: int |
| SystemDataAccess | Function, view | Object accesses system data, system catalogs or virtual system tables, in the local instance of [!INCLUDEssNoVersion]. 0 = None 1 = Read Base data type: int |
| TableDeleteTrigger | Table | Table has a DELETE trigger. >1 = ID of first trigger with the specified type. Base data type: int |
| TableDeleteTriggerCount | Table | The table has the specified number of DELETE triggers. Nonnull = Number of DELETE triggers Base data type: int |
| TableFullTextMergeStatus | Table | Applies to: [!INCLUDEsql2008-md] and later. Whether a table that has a full-text index that is currently in merging. 0 = Table does not have a full-text index, or the full-text index is not in merging. 1 = The full-text index is in merging. |
| TableFullTextBackgroundUpdateIndexOn | Table | Applies to: [!INCLUDEsql2008-md] and later. The table has full-text background update index (autochange tracking) enabled. 1 = TRUE 0 = FALSE Base data type: int |
| TableFulltextCatalogId | Table | Applies to: [!INCLUDEsql2008-md] and later. ID of the full-text catalog in which the full-text index data for the table resides. Nonzero = Full-text catalog ID, associated with the unique index that identifies the rows in a full-text indexed table. 0 = Table does not have a full-text index. Base data type: int |
| TableFullTextChangeTrackingOn | Table | Applies to: [!INCLUDEsql2008-md] and later. Table has full-text change-tracking enabled. 1 = TRUE 0 = FALSE Base data type: int |
| TableFulltextDocsProcessed | Table | Applies to: [!INCLUDEsql2008-md] and later. Number of rows processed since the start of full-text indexing. In a table that is being indexed for full-text search, all the columns of one row are considered as part of one document to be indexed. 0 = No active crawl or full-text indexing is completed. > 0 = One of the following (A or B): A) The number of documents processed by insert or update operations since the start of full, incremental, or manual change tracking population; B) The number of rows processed by insert or update operations since change tracking with background update index population was enabled, the full-text index schema changed, the full-text catalog rebuilt, or the instance of [!INCLUDEssNoVersion] restarted, and so on. NULL = Table does not have a full-text index. Base data type: int Note This property does not monitor or count deleted rows. |
| TableFulltextFailCount | Table | Applies to: [!INCLUDEsql2008-md] and later. The number of rows that full-text search did not index. 0 = The population has completed. >0 = One of the following (A or B): A) The number of documents that were not indexed since the start of Full, Incremental, and Manual Update change tracking population; B) For change tracking with background update index, the number of rows that were not indexed since the start of the population, or the restart of the population. This could be caused by a schema change, rebuild of the catalog, server restart, and so on NULL = Table does not have a Full-Text index. Base data type: int |
| TableFulltextItemCount | Table | Applies to: [!INCLUDEsql2008-md] and later. Nonnull = Number of rows that were full-text indexed successfully. NULL = Table does not have a full-text index. Base data type: int |
| TableFulltextKeyColumn | Table | Applies to: [!INCLUDEsql2008-md] and later. ID of the column associated with the single-column unique index that is part of the definition of a full-text index and semantic index. 0 = Table does not have a full-text index. Base data type: int |
| TableFulltextPendingChanges | Table | Applies to: [!INCLUDEsql2008-md] and later. Number of pending change tracking entries to process. 0 = change tracking is not enabled. NULL = Table does not have a full-text index. Base data type: int |
| TableFulltextPopulateStatus | Table | Applies to: [!INCLUDEsql2008-md] and later. 0 = Idle. 1 = Full population is in progress. 2 = Incremental population is in progress. 3 = Propagation of tracked changes is in progress. 4 = Background update index is in progress, such as autochange tracking. 5 = Full-text indexing is throttled or paused. 6 = An error has occurred. Examine the crawl log for details. For more information, see the Troubleshooting Errors in a Full-Text Population (Crawl) section of Populate Full-Text Indexes. Base data type: int |
| TableFullTextSemanticExtraction | Table | Applies to: [!INCLUDEssSQL11] and later. Table is enabled for semantic indexing. 1 = True 0 = False Base data type: int |
| TableHasActiveFulltextIndex | Table | Applies to: [!INCLUDEsql2008-md] and later. Table has an active full-text index. 1 = True 0 = False Base data type: int |
| TableHasCheckCnst | Table | Table has a CHECK constraint. 1 = True 0 = False Base data type: int |
| TableHasClustIndex | Table | Table has a clustered index. 1 = True 0 = False Base data type: int |
| TableHasDefaultCnst | Table | Table has a DEFAULT constraint. 1 = True 0 = False Base data type: int |
| TableHasDeleteTrigger | Table | Table has a DELETE trigger. 1 = True 0 = False Base data type: int |
| TableHasForeignKey | Table | Table has a FOREIGN KEY constraint. 1 = True 0 = False Base data type: int |
| TableHasForeignRef | Table | Table is referenced by a FOREIGN KEY constraint. 1 = True 0 = False Base data type: int |
| TableHasIdentity | Table | Table has an identity column. 1 = True 0 = False Base data type: int |
| TableHasIndex | Table | Table has an index of any type. 1 = True 0 = False Base data type: int |
| TableHasInsertTrigger | Table | Object has an INSERT trigger. 1 = True 0 = False Base data type: int |
| TableHasNonclustIndex | Table | The table has a nonclustered index. 1 = True 0 = False Base data type: int |
| TableHasPrimaryKey | Table | Table has a primary key. 1 = True 0 = False Base data type: int |
| TableHasRowGuidCol | Table | Table has a ROWGUIDCOL for a uniqueidentifier column. 1 = True 0 = False Base data type: int |
| TableHasTextImage | Table | Table has a text, ntext, or image column. 1 = True 0 = False Base data type: int |
| TableHasTimestamp | Table | Table has a timestamp column. 1 = True 0 = False Base data type: int |
| TableHasUniqueCnst | Table | Table has a UNIQUE constraint. 1 = True 0 = False Base data type: int |
| TableHasUpdateTrigger | Table | The object has an UPDATE trigger. 1 = True 0 = False Base data type: int |
| TableHasVarDecimalStorageFormat | Table | Table is enabled for vardecimal storage format. 1 = True 0 = False |
| TableInsertTrigger | Table | Table has an INSERT trigger. >1 = ID of first trigger with the specified type. Base data type: int |
| TableInsertTriggerCount | Table | The table has the specified number of INSERT triggers. >0 = The number of INSERT triggers. Base data type: int |
| TableIsFake | Table | Table is not real. It is materialized internally on demand by the [!INCLUDEssDE]. 1 = True 0 = False Base data type: int |
| TableIsLockedOnBulkLoad | Table | Table is locked because a bcp or BULK INSERT job. 1 = True 0 = False Base data type: int |
| TableIsMemoryOptimized | Table | Applies to: [!INCLUDEssSQL14] and later. Table is memory optimized 1 = True 0 = False Base data type: int For more information, see In-Memory OLTP (In-Memory Optimization). |
| TableIsPinned | Table | Table is pinned to be held in the data cache. 0 = False This feature is not supported in [!INCLUDEssVersion2005] and later versions. |
| TableTextInRowLimit | Table | Table has text in row option set. > 0 = Maximum bytes allowed for text in row. 0 = text in row option is not set. Base data type: int |
| TableUpdateTrigger | Table | Table has an UPDATE trigger. > 1 = ID of first trigger with the specified type. Base data type: int |
| TableUpdateTriggerCount | Table | Table has the specified number of UPDATE triggers. > 0 = The number of UPDATE triggers. Base data type: int |
| UserDataAccess | Function, View | Indicates the object accesses user data, user tables, in the local instance of [!INCLUDEssNoVersion]. 1 = Read 0 = None Base data type: int |
| TableHasColumnSet | Table | Table has a column set. 0 = False 1 = True For more information, see Use Column Sets. |
| Cardinality | Table (system or user-defined), view, or index | Applies to: [!INCLUDEssSQL11] and later. The number of rows in the specified object. |
| TableTemporalType | Table | Applies to: [!INCLUDEsssql16-md] and later. Specifies the type of table. 0 = non-temporal table 1 = history table for system-versioned table 2 = system-versioned temporal table |
sql_variant
Returns NULL on error or if a caller does not have permission to view the object.
A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECTPROPERTYEX may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.
The [!INCLUDEssDE] assumes that object_id is in the current database context. A query that references an object_id in another database will return NULL or incorrect results. For example, in the following query the current database context is the master database. The [!INCLUDEssDE] will try to return the property value for the specified object_id in that database instead of the database that is specified in the query. The query returns incorrect results because the view vEmployee is not in the master database.
USE master; GO SELECT OBJECTPROPERTYEX(OBJECT_ID(N'AdventureWorks2022.HumanResources.vEmployee'), 'IsView'); GO OBJECTPROPERTYEX(view_id, 'IsIndexable') may consume significant computer resources because evaluation of IsIndexable property requires the parsing of view definition, normalization, and partial optimization. Although the IsIndexable property identifies tables or views that can be indexed, the actual creation of the index still might fail if certain index key requirements are not met. For more information, see CREATE INDEX (Transact-SQL).
OBJECTPROPERTYEX (table_id, 'TableHasActiveFulltextIndex') will return a value of 1 (true) when at least one column of a table is added for indexing. Full-text indexing becomes active for population as soon as the first column is added for indexing.
Restrictions on metadata visibility are applied to the result set. For more information, see Metadata Visibility Configuration.
The following example creates a SYNONYM MyEmployeeTable for the Employee table in the [!INCLUDEssSampleDBobject] database and then returns the base type of the SYNONYM.
USE AdventureWorks2022; GO CREATE SYNONYM MyEmployeeTable FOR HumanResources.Employee; GO SELECT OBJECTPROPERTYEX ( object_id(N'MyEmployeeTable'), N'BaseType')AS [Base Type]; GO The result set shows that the base type of the underlying object, the Employee table, is a user table.
Base Type -------- U The following example returns the number of UPDATE triggers on the specified table.
USE AdventureWorks2022; GO SELECT OBJECTPROPERTYEX(OBJECT_ID(N'HumanResources.Employee'), N'TABLEUPDATETRIGGERCOUNT'); GO The following example uses the TableHasForeignKey property to return all the tables that have a FOREIGN KEY constraint.
USE AdventureWorks2022; GO SELECT name, object_id, schema_id, type_desc FROMsys.objectsWHERE OBJECTPROPERTYEX(object_id, N'TableHasForeignKey') =1ORDER BY name; GO Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]
The following example returns the base type of dbo.DimReseller object.
-- Uses AdventureWorks SELECT OBJECTPROPERTYEX ( object_id(N'dbo.DimReseller'), N'BaseType')AS BaseType; The result set shows that the base type of the underlying object, the dbo.DimReseller table, is a user table.
BaseType -------- U CREATE SYNONYM (Transact-SQL)
Metadata Functions (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL)
OBJECT_ID (Transact-SQL)
OBJECT_NAME (Transact-SQL)
sys.objects (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
TYPEPROPERTY (Transact-SQL)
