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