OBJECTPROPERTYEX
Returns information about schema-scoped objects in the current database. OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped.
Syntax
OBJECTPROPERTYEX ( id , 'property' )
Arguments
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.
| Property name | Object type | Description and values returned |
|---|---|---|
| BaseType | Any schema-scoped object | Not supported |
| SPARK_PARTITIONING | A Spark based DB schema-scoped object | If available returns partitioning configuration for an object. |
| String content or empty string. | ||
| ExecIsAnsiNullsOn | Transact-SQL function, Transact-SQL procedure, Transact-SQL trigger, view | Setting of ANSI_NULLS at creation time. |
| 1 = True | ||
| ExecIsUpdateTrigger | Trigger | UPDATE trigger. |
| 0 = False | ||
| IsIndexed | Table, view | Table or view that has an index. |
| 0 = False | ||
| IsIndexable | Table, view | Table or view on which an index can be created. |
| 0 = False | ||
| IsInlineFunction | Function | Inline function. |
| 0 = Not inline function | ||
| IsQuotedIdentOn | Transact-SQL function, Transact-SQL procedure, table, Transact-SQL trigger, view, CHECK constraint, DEFAULT definition | Specifies that the quoted identifier setting for the object is ON. This means double quotation marks delimit identifiers in all expressions involved in the object definition. |
| 1 = ON | ||
| IsSchemaBound | Function, view | A schema bound function or view created by using SCHEMABINDING. |
| 0 = Not schema-bound. | ||
| IsSystemTable | Table | System table. |
| 1 = True | ||
| 0 = False | ||
| IsTable | Table | Table. |
| 1 = True | ||
| IsUserTable | Table | User-defined table. |
| 1 = True | ||
| 0 = False | ||
| IsView | View | View. |
| 0 = False | ||
| OwnerId | Any schema-scoped object | Owner of the object. Always 1. |
| TableDeleteTrigger | Table | Table has a DELETE trigger. |
| Returns 0. | ||
| TableDeleteTriggerCount | Table | Table has the specified number of DELETE triggers. |
| Returns 0. | ||
| TableFullTextMergeStatus | Table | 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. | ||
| TableFullTextBackgroundUpdateIndexOn | Table | Table has full-text background update index (autochange tracking) enabled. |
| 0 = FALSE | ||
| TableFulltextCatalogId | Table | ID of the full-text catalog in which the full-text index data for the table resides. |
| 0 = Table does not have a full-text index. | ||
| TableFulltextChangeTrackingOn | Table | |
| Table has full-text change-tracking enabled. | ||
| 0 = FALSE | ||
| TableFulltextDocsProcessed | Table | 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. | ||
| This property does not monitor or count deleted rows. | ||
| TableFulltextFailCount | Table | Number of rows Full-Text Search did not index. |
| 0 = The population has completed. | ||
| TableFulltextItemCount | Table | Number of rows that were successfully full-text indexed. |
| Returns 0. | ||
| TableFulltextKeyColumn | Table | ID of the column associated with the single-column unique index that is participating in the full-text index definition. |
| 0 = Table does not have a full-text index. | ||
| TableFulltextPendingChanges | Table | Number of pending change tracking entries to process. |
| 0 = change tracking is not enabled. | ||
| TableFulltextPopulateStatus | Table | 0 = Idle. |
| TableHasActiveFulltextIndex | Table | Table has an active full-text index. |
| 0 = False | ||
| TableHasCheckCnst | Table | Table has a CHECK constraint. |
| 0 = False | ||
| TableHasClustIndex | Table | Table has a clustered index. |
| 0 = False | ||
| TableHasDefaultCnst | Table | Table has a DEFAULT constraint. |
| 0 = False | ||
| TableHasDeleteTrigger | Table | Table has a DELETE trigger. |
| 0 = False | ||
| TableHasIdentity | Table | Table has an identity column. |
| 0 = False | ||
| TableHasIndex | Table | Table has an index of any type. |
| 0 = False | ||
| TableHasInsertTrigger | Table | Object has an INSERT trigger. |
| 0 = False | ||
| TableHasNonclustIndex | Table | Table has a nonclustered index. |
| 0 = False | ||
| TableHasRowGuidCol | Table | Table has a ROWGUIDCOL for a uniqueidentifier column. |
| 0 = False | ||
| TableHasTextImage | Table | Table has a text, ntext, or image column. |
| 0 = False | ||
| TableHasUpdateTrigger | Table | Object has an UPDATE trigger. |
| 0 = False | ||
| TableHasVarDecimalStorageFormat | Table | Table is enabled for vardecimal storage format. |
| 0 = False | ||
| TableInsertTrigger | Table | Table has an INSERT trigger. |
| Returns 0. | ||
| TableInsertTriggerCount | Table | Table has the specified number of INSERT triggers. |
| Returns 0. | ||
| TableIsFake | Table | Table is not real. It is materialized internally on demand by the SQL Server Database Engine. |
| 0 = False | ||
| TableIsLockedOnBulkLoad | Table | Table is locked due to a bcp or BULK INSERT job. |
| 0 = False | ||
| TableIsPinned | Table | Table is pinned to be held in the data cache. |
| 0 = False | ||
| TableTextInRowLimit | Table | Maximum bytes allowed for text in row. |
| Returns 0. | ||
| TableUpdateTrigger | Table | Table has an UPDATE trigger. |
| Returns 0. | ||
| TableUpdateTriggerCount | Table | The table has the specified number of UPDATE triggers. |
| Returns 0. | ||
| TableHasColumnSet | Table | Table has a column set. |
| 0 = False |
Return types
sql_variant
Example
SELECT OBJECTPROPERTYEX(OBJECT_ID(N'AdventureWorks2012.HumanResources.vEmployee'), 'IsView');