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 TableDeleteTrigger Any schema-scoped object Table Owner of the object. Always 1. 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 TableHasActiveFulltextIndex Table Table 0 = Idle. 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');

See Also